-
update trigger cannot get new updated column values
Hi, everybody.
I have a problem with an update trigger.
I need to get the updated values of the columns from the trigger and make some validations but the trigger is not working.
This is the trigger code:
CREATE OR REPLACE TRIGGER "PROF"."TRIG_UPDATE_EMERG"
BEFORE UPDATE ON "PROF"."EMERG"
REFERENCING OLD AS NEW NEW AS OLD FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('new value of id_estado is '||:NEW.ID_ESTADO||' and new value of ID_MUNICIPIO= '||:NEW.ID_MUNICIPIO );
END;
I want that the trigger prints the new values that i'm applying in the update command. As you will see the trigger shows the old values in the referencing NEW variable instead the new values.
Some ideas??
Here are some update sample commands that I was testing:
SQL> update emerg set id_municipio=3 where id_emergencia=9;
new value of id_estado is 4 and new value of ID_MUNICIPIO= 1
1 row updated.
SQL> commit;
Commit complete.
SQL> update emerg set id_municipio=1000 where id_emergencia=9
new value of id_estado is 4 and new value of ID_MUNICIPIO= 3
1 row updated.
Thanks in advance.
Regards
-
TRY....
sql> set serveroutput on
SQL> update emerg set id_municipio=3 where id_emergencia=9;
new value of id_estado is 4 and new value of ID_MUNICIPIO= 1
HTH
Gregg
-
hehehehe, I found my big mistake.
I'm referencing old value as NEW.
Sorry.
Thanks again.
-
Re: update trigger cannot get new updated column values
Originally posted by Turin
This is the trigger code:
CREATE OR REPLACE TRIGGER "PROF"."TRIG_UPDATE_EMERG"
BEFORE UPDATE ON "PROF"."EMERG"
REFERENCING OLD AS NEW NEW AS OLD FOR EACH ROW
BEGIN
...
Gee, this bold part realy deserves to be quoted in some other thread, named "How to confuse developers" (http://www.dbasupport.com/forums/sho...threadid=39780)
Change that part into "REFERENCING OLD AS old NEW AS new" or remove it from your CREATE TRIGGER command and you'll get what you want.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|