Hello

I have an update trigger that is for each row.
The goal is to detect if a timestamp field is updated, if not updated
it with system timestamp.
My problem is the value of this field is lost and I end up with null
value in the table.
I am attaching my testing script
I am using Oracle 10g.

Thanks for your help.
CREATE OR REPLACE TRIGGER TU_FORWARD_FX_RATE_TS
BEFORE UPDATE ON FORWARD_FX_RATE_TS referencing new as new, old as
old
FOR EACH ROW
--WHEN (new.last_modified_timestamp is null)

BEGIN
insert into TABLE_LOG values('Trigger', systimestamp,
0, :new.LAST_MODIFIED_TIMESTAMP);
insert into TABLE_LOG values('Trigger', systimestamp,
1, ld.LAST_MODIFIED_TIMESTAMP);

IF not updating('LAST_MODIFIED_TIMESTAMP') THEN
insert into TABLE_LOG values('Trigger', systimestamp, 2, 'not
updating');
:new.last_modified_timestamp := SYSTIMESTAMP;
END IF;


END;


Test script:
-- 1st test
update FORWARD_FX_RATE_TS set last_modified_timestamp = '17-may-1974'
where CURRENCY_ID = 'CHF' and as_of_date = '29-Jun-2007';
-- 2nd test
--update FORWARD_FX_RATE_TS set as_of_date = '29-Jun-2007' where
CURRENCY_ID = 'CHF' and as_of_date = '29-Jun-2007';
commit;

-- Check value in the table
select * from FORWARD_FX_RATE_TS where CURRENCY_ID = 'CHF' and
as_of_date = '29-Jun-2007';

-- Check the logs
select * from table_log where LABEL = 'Trigger';
-- Cleaning
delete table_log where LABEL = 'Trigger';
commit;

drop trigger TU_FORWARD_FX_RATE_TS;

Menthe&Basilic wrote:
Get rid of the WHEN clause and use NVL on :OLD.last_modified_timestamp.
http://www.psoug.org/reference/misc_func.html#mfnv
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org