RE: Strange DB Trigger behavior

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Mon, 13 Jul 2009 14:11:56 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA98A_at_EXCNYSM0A1AJ.nysemail.nyenet>

Thanks Toon. Others have written the exact same thing. I guess my memory is playing tricks on me.  

Tom  

---

From: Toon Koppelaars [mailto:toon.koppelaars_at_rulegen.com] Sent: Monday, July 13, 2009 2:00 PM
To: Mercadante, Thomas F (LABOR)
Cc: ORACLE-L
Subject: Re: Strange DB Trigger behavior  

        I would expect that when a row is being updated and a modify_date was not provided in the update statement, that ...

I think you should change this to:

I would expect that when a row is being updated and a modify_date was provided and assigned to NULL in the update statement, that ...

Haven't tested it though, but it's what I would suspect.

On Mon, Jul 13, 2009 at 7:54 PM, Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us> wrote:

All,  

I am running a 10.2.0.2 database on Aix.  

I have standard db triggers on tables that are not acting as I expect. Please give me a sanity check:  

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.  

Does this make sense to you? My memory might be slipping, but that is what I would expect.

Thanks

Tom  

-- 
Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13


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

Received on Mon Jul 13 2009 - 13:11:56 CDT