update trigger cannot get new updated column values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: update trigger cannot get new updated column values

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    Exclamation 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

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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

  3. #3
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    Talking

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

    Sorry.
    Thanks again.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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
  •  



Click Here to Expand Forum to Full Width