Trigger deleting :new value

From: Menthe&Basilic <sbouaida_at_yahoo.fr>
Date: Tue, 06 Nov 2007 01:45:13 -0800
Message-ID: <1194342313.846314.25350@22g2000hsm.googlegroups.com>

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, :old.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; Received on Tue Nov 06 2007 - 03:45:13 CST