Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Denmark
    Posts
    4

    Question Unanswered: can't use NEW or OLD in triggers

    I'm trying to create a trigger, which change a field in a row after an update on the table. I thought it was possible to access the updated row by :NEW.field and assigning a new value, but I get an error :

    NEW or OLD references not allowed in table level triggers

    the code for the trigger is similar to the following:

    CREATE TABLE tableTest (id number primary key, test_string varchar2(10));

    CREATE OR REPLACE TRIGGER trigTest
    AFTER UPDATE ON tableTest
    REFERENCING NEW AS newRow
    BEGIN
    :newRow.test_string = 'succes'
    END trig1;
    .
    run;

    I'm not sure if it is possible to use the statement :newRow.test_string = 'succes' or I have to use something like update tableTest set test_string='succes' where id=:newRow.id, but then the table get mutated...

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    You need to specify that the trigger should be fired FOR EACH ROW. Also, you might want to use a BEFORE trigger if you would like to modify the :new values

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: can't use NEW or OLD in triggers

    You forgot the "FOR EACH ROW" clause, apart from making some other syntax errors. Also, if you want to change the NEW values it has to be a BEFORE trigger:

    CREATE OR REPLACE TRIGGER trigTest
    BEFORE UPDATE ON tableTest
    FOR EACH ROW
    BEGIN
    :new.test_string := 'success';
    END trig1;

    (I can never see the point of using that REFERENCING clause myself.)
    Last edited by andrewst; 10-31-03 at 10:28.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try something like this ... Also, you assigment of 'succes' NEEDS TO
    BE DONE LIKE. ...... := 'succes'

    CREATE OR REPLACE TRIGGER trigTest
    AFTER UPDATE ON tableTest
    REFERENCING NEW AS NEWDATA OLD AS OLDDATA
    FOR EACH ROW
    BEGIN
    :newRow.test_string := 'succes'
    END trig1;


    hth
    Gregg

  5. #5
    Join Date
    Oct 2003
    Location
    Denmark
    Posts
    4
    Thanks.

    You all posted that I need to use the FOR EACH ROW. In my case I only need to set test_string := 'succes' in the row which have been updated. Is there anyway I can achieve this.
    I could just update that field when I update the row, but I prefer not.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    FOR EACH ROW means "for each row updated", not for every row in the table whether updated or not! The trigger above does exactly what you want.

    Without FOR EACH ROW, the trigger fires ONCE per update statement. That's why it can't use the NEW and OLD values, since there may be more than one row affected by the statement.

  7. #7
    Join Date
    Oct 2003
    Location
    Denmark
    Posts
    4
    Thanks again.

    I was not aware of the fact that FOR EACH ROW only worked on the updated rows. That fact really helped me.

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    FOR EACH ROW means that the trigger will exectue for each row. So if you have an update trigger it will fire for every row updated, but when u have a delete trigger it will fire for every row deleted, and when u have an insert trigger .....
    Hope u get the point.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  9. #9
    Join Date
    Oct 2003
    Location
    Denmark
    Posts
    4
    I think i got it now. EACH ROW will only execute on the rows being updated, deleted or inserted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •