Email not sent through trigger on INSERT using utl_mail
781163Jul 7 2010 — edited Jul 9 2010Hi,
I have a trigger which is responsible to send an email whenever a particular column(ATTRIBUTE11) is inserted with a value or updated.
Email should be sent when the table(ATTRIBUTES) is inserted with a value in ATTRIBUTE11(i.e, ATTRIBUTE11 is not null)
An email should not be sent on update of the same value in that column(old.ATTRIBUTE11=new.ATTRIBUTE11) and also an email should not be sent if the table is inserted without a value in ATTRIBUTE11
Table ATTRIBUTES has a structure:
Account varchar2(20);
Attribute11 varchar2(20);
user_name varchar2(20);
Trigger:
CREATE OR REPLACE TRIGGER "TPP_SND_EMAIL"
AFTER INSERT OR UPDATE OF ATTRIBUTE11
ON GENEVA_ADMIN.ATTRIBUTES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
OLD.ATTRIBUTE11<>NEW.ATTRIBUTE11
)
DECLARE
msgbody varchar2(50);
BEGIN
utl_mail.send(
sender => 'hemanth11"yahoo.co.in',
recipients => 'hemant_11@gmail.com',
subject => 'Testing mail through utl_mail.send',
message => msgbody,
mime_type => 'text/plain'
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error: '||sqlerrm);
END TPP_SND_EMAIL;
/
*An Email is not sent whenever a new row is inserted into this table with a value in ATTRIBUTE11(This should happen).*
The trigger fires an email successfully when ever an update takes place on the column.
Do we have an option to handle the when clause differently for INSERT and differently for UPDATE or is theer a possible way to handle the same in the trigger above.
Please assist me in this regard.
Edited by: user13321434 on Jul 7, 2010 6:19 AM