Home » SQL & PL/SQL » SQL & PL/SQL » how to update a field in an insert trigger
how to update a field in an insert trigger [message #201785] Mon, 06 November 2006 13:33 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

i am trying to update a field after a function is called but the trigger is not inserting value into field, i used dbms_output line and the values are there but it will not update the record, the email_ids do not equal in the where clause what am i missing?

CREATE OR REPLACE TRIGGER EMAIL_SENT_TRIG
AFTER insert ON EMAIL_SEND
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
P_EMAIL_RESULT VARCHAR2(250);
v_id number;

BEGIN

P_EMAIL_RESULT:= TEXT_EMAIL(:NEW.E_FROM, :NEW.E_TO, NULL, :NEW.E_SUBJECT, :NEW.E_MESSAGE);

dbms_output.put_line(' '||:new.email_id||' '||p_email_result);
UPDATE EMAIL_SEND
SET EMAIL_RESULTS = P_EMAIL_RESULT
WHERE EMAIL_ID = :NEW.EMAIL_ID;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Re: how to update a field in an insert trigger [message #201789 is a reply to message #201785] Mon, 06 November 2006 14:06 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Thats not how you write a trigger, you may NOT perform an update on the triggering table. try

CREATE OR REPLACE TRIGGER EMAIL_SENT_TRIG
AFTER insert ON EMAIL_SEND
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
P_EMAIL_RESULT VARCHAR2(250);
v_id number;

BEGIN

:new.EMAIL_RESULT:= TEXT_EMAIL(:NEW.E_FROM, :NEW.E_TO, NULL, :NEW.E_SUBJECT, :NEW.E_MESSAGE);

dbms_output.put_line(' '||:new.email_id||' '||:new.email_result);

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

[Updated on: Mon, 06 November 2006 14:07]

Report message to a moderator

Re: how to update a field in an insert trigger [message #201791 is a reply to message #201789] Mon, 06 November 2006 14:37 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks for the quick response but I am now receiving an error ORA-04084, however changing the trigger from after insert on to before insert on WORKED! Its now working correctly thanks.
Re: how to update a field in an insert trigger [message #201794 is a reply to message #201785] Mon, 06 November 2006 15:00 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Sorry, when I rewrote the trigger I had not noticed that the original trigger was an after trigger. To modify the data in a new row, you must use a before trigger, as you found out.
Re: how to update a field in an insert trigger [message #201829 is a reply to message #201794] Tue, 07 November 2006 00:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
one more thing: get rid of the dbms_output before you go live with this..
Previous Topic: Trigger for capture the information
Next Topic: Column to Row Transpose - Efficient Code Required
Goto Forum:
  


Current Time: Sun Dec 04 06:47:48 CST 2016

Total time taken to generate the page: 0.08761 seconds