Home » SQL & PL/SQL » SQL & PL/SQL » oracle Trigger for E-mail notifications !! (oracle 11g)
oracle Trigger for E-mail notifications !! [message #668938] Sun, 25 March 2018 04:40 Go to next message
shipon_97
Messages: 16
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 #668939 is a reply to message #668938] Sun, 25 March 2018 04:45 Go to previous messageGo to next message
John Watson
Messages: 8120
Registered: January 2010
Location: Global Village
Senior Member
What code have you tried so far? Where is the problem?

You should consider whether this is actually a good idea. What if, for example, a row is inserted and your email is sent, and then the insert is rolled back?
Re: oracle Trigger for E-mail notifications !! [message #668940 is a reply to message #668939] Sun, 25 March 2018 05:13 Go to previous messageGo to next message
shipon_97
Messages: 16
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 #668941 is a reply to message #668940] Sun, 25 March 2018 05:19 Go to previous messageGo to next message
John Watson
Messages: 8120
Registered: January 2010
Location: Global Village
Senior Member
You have forgotten to show the error.
Re: oracle Trigger for E-mail notifications !! [message #668942 is a reply to message #668941] Sun, 25 March 2018 05:28 Go to previous messageGo to next message
shipon_97
Messages: 16
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 Go to previous messageGo to next message
John Watson
Messages: 8120
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.

Re: oracle Trigger for E-mail notifications !! [message #668945 is a reply to message #668940] Sun, 25 March 2018 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Replace:
SELECT SMS_FLAG into ls_count from smstab  ; 
IF ls_count = 101 THEN 
by
IF :NEW.SMS_FLAG = 101 THEN
You cannot select the table which is triggering in a EACH ROW trigger.
(Without speaking about the fact that you are selecting the WHOLE table.)

Re: oracle Trigger for E-mail notifications !! [message #668946 is a reply to message #668945] Sun, 25 March 2018 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also "END IF;" is missing.

Re: oracle Trigger for E-mail notifications !! [message #668951 is a reply to message #668940] Sun, 25 March 2018 16:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Also, you can use :NEW.column_name instead of selecting from the table, so you can replace:

SELECT SMS_FLAG into ls_count from smstab ;
IF ls_count = 101 THEN

with:

IF :NEW.SMS_FLAG = 101 THEN

Re: oracle Trigger for E-mail notifications !! [message #668954 is a reply to message #668940] Sun, 25 March 2018 17:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
You can also use a WHEN clause instead of IF, so that if saves running the rest of the trigger code if the condition is not met, so your whole trigger could be shortened to the code below.

CREATE OR REPLACE TRIGGER sms_notification
  after insert on SMSTAB
  for each row 
  WHEN (NEW.SMS_FLAG = 101)
BEGIN 
  utl_mail.send(        
    sender     => 'test',
    recipients => 'test@domain.com',
    subject    => 'SMS is not Working',
    message    => 'SMS is not Working... ...');
End sms_notification;
/
Re: oracle Trigger for E-mail notifications !! [message #668975 is a reply to message #668954] Mon, 26 March 2018 23:57 Go to previous message
shipon_97
Messages: 16
Registered: March 2008
Junior Member
Thanks a lot
Previous Topic: How to log error using UTL_CALL_STACK
Next Topic: ORA-21561: OID generation failed !!
Goto Forum:
  


Current Time: Sun Jan 19 22:19:32 CST 2020