Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!crtntx1-snh1.gtei.net!mtvwca1-snh1.ops.genuity.net!paloalto-snf1.gtei.net!news.gtei.net!columbine.singnet.com.sg!not-for-mail
From: AcCeSsDeNiEd <dillon@rm_accessdenied.darktech.org>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Trigger Update Date
Date: Thu, 02 Jan 2003 10:58:06 +0800
Organization: Singapore Telecommunications Ltd
Lines: 70
Message-ID: <qha71v4nhgae4sjfmhm5hdc42fukrolmcj@4ax.com>
References: <15771vc242lev92o7s7i13c6jhk2rp6cjq@4ax.com>
Reply-To: dillon@rm_accessdenied.darktech.org
NNTP-Posting-Host: 203.125.115.30
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.92/32.572
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:90983
X-Received-Date: Wed, 01 Jan 2003 20:24:28 MST (news.easynews.com)

Ok I got it.

Just needed more caffeine :)

For the benefit of google searches, here's the solution:

create or replace trigger mod_ded_trig
before UPDATE OF NAME1 ON ADMIN.TEST
for each row
begin
:new.Mod_DED := SYSDATE;
end;


On Thu, 02 Jan 2003 10:21:06 +0800, AcCeSsDeNiEd <dillon@rm_accessdenied.darktech.org> wrote:

>Oracle noob here.
>
>I'm trying to create a trigger that will update the "Mod_Date" field when a row of data is updated.
>
>So far I've come up with this:
>
>
>CREATE or replace TRIGGER mod_ded_trig 
>BEFORE UPDATE OF NAME1 ON ADMIN.TEST
>BEGIN
>update ADMIN.TEST set Mod_DED=sysdate;
>END;
>
>
>Running the query:
>
>update admin.test set Name1='MyName' where Name1='OtherName';
>
>
>This ends up updating all the rows of data in the 'admin.test' table with the new date regardless or
>not the row is updated (via NAME1).
>
>I just wanna update the mod_date of only the rows that are affected.
>
>
>So I replaced the trigger with this:
>
>CREATE or replace TRIGGER mod_ded_trig 
>BEFORE UPDATE OF NAME ON ADMIN.TEST
>for each row
>BEGIN
>update ADMIN.TEST set Mod_DED=sysdate;
>END;
>
>
>This produces an error when I attempt to update a row:
>
>"ORA-04091: table ADMIN.TEST is mutating, trigger/function may not see it
>ORA-06512: at "MOD_DED_TRIG", line 2
>ORA-04088: error during execution of trigger 'MOD_DED_TRIG'
>
>
>Any ideas / sample code?
>I've searched google and my books and came up with nothing.
>
>Thanks.
>
>
>
>
>To e-mail me, remove "rm_"


To e-mail me, remove "rm_"