Re: Strange DB Trigger behavior

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 14 Jul 2009 08:15:18 -0700
Message-ID: <bf46380907140815o3385c0f3r6cce7e5025a6c84d_at_mail.gmail.com>

On Mon, Jul 13, 2009 at 10:54 AM, Mercadante, Thomas F (LABOR) < Thomas.Mercadante_at_labor.state.ny.us> 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;
end;

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

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