Re: Trigger Problem !

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/02
Message-ID: <357400a0.2074242@192.86.155.100>#1/1

A copy of this was sent to Peter Cheung <petepat_at_aracnet.net> (if that email address didn't require changing) On Mon, 01 Jun 1998 22:16:23 -0400, you wrote:

> I created a table with the following trigger in Oracle 8.0.3 on a NT
>machine:
>
>CREATE OR REPLACE TRIGGER "BEF_ROW_UPDT_TS" BEFORE INSERT OR UPDATE OF
>"DESCRIPTION", "ID" ON "TTABLE" REFERENCING OLD AS OLD NEW AS NEW FOR
>EACH ROW BEGIN :new.LASTUPDTDATE:=SYSDATE;:new.REFCOUNTER:=
>:old.REFCOUNTER+1;
>END
>
>;
>

In sqlplus type:

show errors trigger bef_row_updt_ts

or select * from user_errors to see the compilation error you recieved.

btw: once you get it to compile, it'll not work :)

You have this as a before INSERT trigger and you are referencing :old.refcounter in it. You'll need to do something like:

  if (updating) then

     :new.refcounter := :old.refcounter+1;   else

     :new.refcounter := 1;
  end if;

> However, when I tried to update the "DESCRIPTION" of "TTABLE", I
>received the following error message:
>
>SQLSTATE: S1000
>Native Error Code: 4098
>Driver Message: ORA-04098 'BEF_ROW_UPDT_TS' is invalid and failed
>re-validation.
>
> I could not figure out what went wrong ? Please help. Thx.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 02 1998 - 00:00:00 CDT