oracle Trigger for E-mail notifications !! [message #668938] |
Sun, 25 March 2018 04:40 |
shipon_97
Messages: 17 Registered: March 2008
|
Junior Member |
|
|
dear friends ,
For a particular column insert , I want to create a trigger for e-mail notification .
Suppose , I have table "SMSTAB" where there is a column named " STATUS " , which has two options "FAIL" and "SUCCESS" . In this case , I want to generate a oracle 11g e-mail notification when status='ERROR' .
Need your kind help to this !!!
|
|
|
|
Re: oracle Trigger for E-mail notifications !! [message #668940 is a reply to message #668939] |
Sun, 25 March 2018 05:13 |
shipon_97
Messages: 17 Registered: March 2008
|
Junior Member |
|
|
I try to create a trigger , would you Please help me :
CREATE OR REPLACE TRIGGER sms_notification
after insert on SMSTAB
for each row
DECLARE
ls_message VARCHAR2(1000); -- Error message to be printed
ls_count pls_integer;
e_max_user EXCEPTION; -- Bad user exception
BEGIN
-- Get info from USERENV context
SELECT SMS_FLAG into ls_count from smstab ;
IF ls_count = 101 THEN
begin
utl_mail.send(
sender => 'test',
recipients => 'test@domain.com',
subject => 'SMS is not Working',
message => 'SMS is not Working... ...'
);
end;
End;
--moderator update: added [code] tags, please do so yourself in future.
[Updated on: Sun, 25 March 2018 05:18] by Moderator Report message to a moderator
|
|
|
|
Re: oracle Trigger for E-mail notifications !! [message #668942 is a reply to message #668941] |
Sun, 25 March 2018 05:28 |
shipon_97
Messages: 17 Registered: March 2008
|
Junior Member |
|
|
is my logic is right or not ?
Here is the Error :
SQL> show error
Errors for TRIGGER SMS_NOTIFICATION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "SELECT" to continue.
18/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare else elsif end exit for goto if loop mod
null pragma raise return select update while with
LINE/COL ERROR
-------- -----------------------------------------------------------------
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
|
|
|
Re: oracle Trigger for E-mail notifications !! [message #668943 is a reply to message #668942] |
Sun, 25 March 2018 06:05 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Please use [code] tags when you paste that sort of thing. If you don't know how, read this How to use code tags and make your code easier to read I shall not reply to any messages that not properly formatted.
You have now forgotten to mention which is line 7. You need to show the whole session: what you did and what went wrong.
In general, though, I would advise a different approach. Do not try to write the whole trigger in one go. Start with something really simple:orclx>
orclx> create or replace trigger sms_notification
2 after insert on smstab
3 begin
4 null;
5 end;
6 /
Trigger created.
orclx> and then add the logic line by line.
|
|
|
|
|
|
|
|