Re: Strange DB Trigger behavior

From: Jared Still <>
Date: Tue, 14 Jul 2009 08:15:18 -0700
Message-ID: <>

On Mon, Jul 13, 2009 at 10:54 AM, Mercadante, Thomas F (LABOR) <> wrote:

> create or replace trigger tran_data_biu
> before insert or update
> on tran_Data
> for each row
> begin
> if updating then
> if :new.modify_date is null then
> :new.modify_date := sysdate;
> end if;
> end if;
> end;
> I would expect that when a row is being updated and a modify_date was not
> provided in the update statement, that the above trigger would fill the
> column in with sysdate. But this is not happening. The only time the above
> happens is when the column is null the first time. After a date exists, the
> “new” value is populated with the “old” value.

Perhaps this will do what you had in mind.

if updating then
  if :old.modify_date is null or :new.modify_date is null then

     :new.modify_date := sysdate;
   end if;

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Tue Jul 14 2009 - 10:15:18 CDT

Original text of this message