trigger old/new values

DGS

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

MichaelS

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;
...

thomaso

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

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

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

Frank,
I've just realized that you meant "it" - the value supplied by the user, not the trigger action...
Sorry
Thomas

Frank Kulash

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

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

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

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

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

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.