Re: Trigger deleting :new value

Menthe&Basilic wrote:
> 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;
>

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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org