update trigger cannot get new updated column values

[Turin]

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

[gbrabham]

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

[Turin]

hehehehe, I found my big mistake.
I'm referencing old value as NEW.

Sorry.
Thanks again.

[jmodic]

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.