Forum Stats

  • 3,838,084 Users
  • 2,262,326 Discussions
  • 7,900,498 Comments

Discussions

trigger old/new values

DGS
DGS Member Posts: 30
edited Feb 18, 2010 11:36AM in SQL & PL/SQL
I have some STAMP columns in a table that I would like to populate with a trigger so that they are consistently populated.

On insert
STAMP_ADD_DATE
STAMP_ADDED_BY

On update
STAMP_UPDATE_DATE
STAMP_UPDATED BY

My problem is occuring on the update portion. Here is the text:
IF :new.stamp_updated_by IS NULL THEN
:new.stamp_updated_by := sys_context('USERENV', 'OS_USER');
END IF;
:new.stamp_update_date := d_today;

9 out of 10 times our .NET front end will pass in a value for :NEW.STAMP_UPDATED_BY to the procedure. That works great.

When I perform an update from SQL*Plus and omit the column from the update statement, it is reusing the :OLD.STAMP_UPDATED_BY value from the original record.

I would have expected it to realize that I have not referenced that column and fallen into the NULL condition in the trigger. Like a column default value..

Is there a way, without specifying the column name in the update statement to get this column to fall into the NULL condition if it is not specified in the update statement?


Thanks,
ds
Tagged:

Best Answer

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    When I perform an update from SQL*Plus and omit the column from the update statement, it is reusing the :OLD.STAMP_UPDATED_BY value from the original record.

    I would have expected it to realize that I have not referenced that column and fallen into the NULL condition in the trigger. Like a column default value..
    I looks you need
    ...
    IF not updating ('stamp_updated_by') or  :new.stamp_updated_by IS NULL THEN
    :new.stamp_updated_by := sys_context('USERENV', 'OS_USER');
    END IF;
    ...
«1

Answers

  • thomaso
    thomaso Member Posts: 349
    Try
    IF :new.stamp_updated_by = :old.stamp_updated_by  THEN
       :new.stamp_updated_by := null;
    END IF;
    
    IF :new.stamp_updated_by IS NULL THEN
    :new.stamp_updated_by := sys_context('USERENV', 'OS_USER');
    END IF;
    :new.stamp_update_date := d_today;
    HTH
    Thomas
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,224 Red Diamond
    edited Feb 18, 2010 10:11AM
    Hi
    [email protected] wrote:
    ...
    When I perform an update from SQL*Plus and omit the column from the update statement, it is reusing the :OLD.STAMP_UPDATED_BY value from the original record.

    I would have expected it to realize that I have not referenced that column and fallen into the NULL condition in the trigger. Like a column default value..
    Unfortunately (for you) that's not how it works. :NEW always includes all columns, whether or not they were specifically mentioned in the DML statement.
    Is there a way, without specifying the column name in the update statement to get this column to fall into the NULL condition if it is not specified in the update statement?
    No, I don't know of anything. Always specify the column, and explicitly set it to NULL when you want the defualt behavior.

    If you want to always have the trigger populate that column, just remove the IF and END IF from around
    :new.stamp_updated_by := sys_context('USERENV', 'OS_USER');
    If the user does supply a value, it will be ignored. This is commonly done with "stamp" columns.

    Edited by: Frank Kulash on Feb 18, 2010 10:09 AM
    I just noticed, you're already doing the same sort of unconditional fill-in for another column:
    :new.stamp_update_date := d_today;
  • thomaso
    thomaso Member Posts: 349
    Frank,
    If the user does supply a value, it will be ignored. <<
    Are you sure?
    SQL> create table test_01 ( a varchar2(10) );
     
    Table created
     
    SQL> 
    SQL> CREATE OR REPLACE TRIGGER test_01$B$IU BEFORE UPDATE ON test_01
      2  FOR EACH ROW
      3  BEGIN
      4  	:new.a := 'B';
      5  END;
      6  /
     
    Trigger created
     
    SQL> insert into test_01 (a ) values ('C');
     
    1 row inserted
     
    SQL> update test_01 set a = 'D';
     
    1 row updated
     
    SQL> select * from test_01;
     
    A
    ----------
    B
    HTH
    Thomas
  • thomaso
    thomaso Member Posts: 349
    Frank,
    I've just realized that you meant "it" - the value supplied by the user, not the trigger action...
    Sorry
    Thomas
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,224 Red Diamond
    edited Feb 18, 2010 9:31AM
    thomaso wrote:
    Frank,
    If the user does supply a value, it will be ignored. <<
    Are you sure?
    Yes. Isn't that exactly what you just demonstrated?
    SQL> create table test_01 ( a varchar2(10) );
    
    Table created
    
    SQL> 
    SQL> CREATE OR REPLACE TRIGGER test_01$B$IU BEFORE UPDATE ON test_01
    2  FOR EACH ROW
    3  BEGIN
    4  	:new.a := 'B';
    5  END;
    6  /
    
    Trigger created
    
    SQL> insert into test_01 (a ) values ('C');
    
    1 row inserted
    
    SQL> update test_01 set a = 'D';
    
    1 row updated
    
    SQL> select * from test_01;
    
    A
    ----------
    B
    HTH
    Thomas
    The user-supplied value 'D' was ignored, and the trigger-supplied value 'B' was used instead, replacing the 'C' that had been there.
  • DGS
    DGS Member Posts: 30
    Problem here is that the same person could update the record twice.

    Previous value was 'DAN' (from .NET)
    New value is 'DAN' (from .NET), stamp gets changed to OS_USER, whic is something like ASPNET.
  • DGS
    DGS Member Posts: 30
    Sounds like I'm sunk here. Does everyone agree?
    I was hoping I could intercept the :NEW.value BEFORE it became the :OLD.value.

    Sounds like I need a BEFORE, BEFORE Update trigger...


    Thanks folks for the quick sanity check.
  • bluefrog
    bluefrog Member Posts: 1,512
    The value remains as DAN, using Thomas's table & trigger, try the following:
    SQL> CREATE OR REPLACE TRIGGER test_01$B$IU BEFORE UPDATE ON test_01
      2      FOR EACH ROW
      3      BEGIN
      4       if (:old.a = :new.a) then
      5        null;
      6       else
      7        :new.a := 'OSUSER';
      8       end if;
      9  END;
     10  /
    
    Trigger created.
    
    SQL> insert into test_01 (a) values ('DAN');
    
    1 row created.
    
    SQL> select * from test_01;
    
    A
    ----------
    DAN
    
    SQL> insert into test_01 (a) values ('OTHER');
    
    1 row created.
    
    SQL> select * from test_01;
    
    A
    ----------
    DAN
    OTHER
    
    SQL> update test_01 set a='DAN' where a='DAN';
    
    1 row updated.
    
    SQL> select * from  test_01;
    
    A
    ----------
    DAN
    OTHER
    
    -- It changes if the values are different!
    SQL> update test_01 set a='SOME' where a='OTHER';
    
    1 row updated.
    
    SQL> select * from  test_01;
    
    A
    ----------
    DAN
    OSUSER
    As you can see, 'DAN' remains 'DAN'. Isn't that what you wanted? Thomas gave you the answer with his first post! So why are you sunk?
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,224 Red Diamond
    edited Feb 18, 2010 10:57AM
    Hi,
    [email protected] wrote:
    Sounds like I'm sunk here. Does everyone agree?
    I don't agree that you have to live without certain functionality. You may not be able to achieve it exactly the way you first thought, however.

    Let me make sure I uderstand the problem.
    Sometimes, you want the trigger to automatically fill in the stamp_updated_by column. You will indicate this by issuing UPDATE statement A.
    At other times, you want to fill in that column yourself. Youl will indicate this by issuing UPDATE statement B, which is slightly diofferent from statement A.
    Did I state the problem right?

    You can do that with a trigger.

    You originally asked if statement A could be this (let's call it statement A1):
    UPDATE  table_x
    SET     amt = 100
    WHERE   x_id = 2;
    and statment B could be this (B1):
    UPDATE  table_x
    SET     amt = 100;
    ,       stamp_updated_by = 'FUBAR'
    WHERE   x_id = 2;
    and the answer was: "No, a trigger can't necessarily tell if the DML statement referenced stamp_updated_by explicitly."

    You can get the desired functionality if statement A is A2:
    UPDATE  table_x
    SET     amt = 100
    ,       stamp_updated_by = NULL
    WHERE   x_id = 2;
    and statement B2 is exactly the same as B1:
    UPDATE  table_x
    SET     amt = 100;
    ,       stamp_updated_by = 'FUBAR'
    WHERE   x_id = 2;
    One way to get that functionality is to have the following code in the trigger:
    :NEW.stamp_updated_by := COALESCE ( :NEW.stamp_updated_by
    		      	 	  , sys_context ('USERENV', 'OS_USER')
    				  );
    In some ways, the A2-B2 way is better than A1-B1; you can have a single UPDATE statement serve as both A2 and B2, and just use a bind variable to distinguish them.
    This does assume that stamp_updated_by can not be NULL. If you want to allow people to explicitly set stamp_updated_by to NULL, then you need something more.
  • DGS
    DGS Member Posts: 30
    I was starting to like that comparison of OLD and NEW but it doesn't work if the same person updates the record twice.

    1st update STAMP_UPDATED_BY column specified in SQL and it is set to 'SALLY'

    2nd update STAMP_UPDATED_BY specified and it is also set to 'SALLY'
    This comparison sees that OLD and NEW are the same and bypasses the specified value and substitutes
    the specified value with the sys_context('USERENV', 'OS_USER');

    Reset the STAMP_UPDATED_BY to 'SALLY'

    update and do not specify a STAMP_UPDATED_BY this solution works great. OS_USER is added, great
    update again and set the STAMP_UPDATED_BY = 'SALLY'. works great, SALLY is added
    update again and set the STAMP_UPDATED_BY = 'SALLY' <-- this is where the problems happen. SALLY is not added but instead OS_USER is added.
This discussion has been closed.