Re: Need a SQL expert help : How to create a trigger for DELETE ???????

From: James Arvigo <Hooper_X_at_Spam_Rage.com>
Date: 1998/08/12
Message-ID: <35D216F3.1C9B8384@Spam_Rage.com>#1/1

Eric,

In Oracle your "On Delete" trigger might look something like this:

CREATE OR REPLACE TRIGGER scott.dept_bd_rtrg BEFORE DELETE
ON SCOTT.DEPT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
    nSomeVal NUMBER(9);
    nSomeStr VARCHAR2(30);
BEGIN
   INSERT INTO scott.other_table VALUES (

      :old.field1,
      :old.field2,
      :old.field3,
      :old.field4,
      etc.
       .
       .
       .);

END dept_bd_rtrg;

In the above example...
SCOTT is the table owner
DEPT is the table name
OTHER_TABLE is the table you're copying over to DEPT_BD_RTRG is the trigger name
field1, field2, etc. are table columns

On trigger name, "dept_bd_rtrg", I've applied a naming convention as follows:

       table_name + event type + trigger type + trigger suffix
i.e.       dept   +    _bd_      +     r      + trg

in which "dept" = the table name, "_bd_" is meant to signify that it is a

"Before Delete", versus "ad" for After Delete, "bi" for Before Insert, etc... and "_r" signifies that is a Row Level trigger rather than Statement Level, i.e. "_strg", and "trg" indicates that this is a Trigger versus any other type of DB object.

Of course you could name it however you want.

But this method helps me to identify and distinguish my DB objects easilly.

Hope that helps...

--
James Arvigo

============================================================


* SQL Server & Oracle DBA

* Software & Intranet Developer

* Thrifty Call, Inc. via The Maxim Group

* Austin, Texas

*----------------------------------------------------------


* EMAILS:

* Work: JamesA  -AT- ThriftyCall.com

* Home: JArvigo -AT- Hotmail.com

*


* ( Sorry I had obfuscate the emails... *sigh* )

============================================================

Eric, Chow Hoi Ka wrote:

> Hello,
>
> How can I create a TRIGGER for DELETE a record ?
>
> Such as, when I delete a record, it will auto copy this record to
> another TABLE ???
>
> Best regards,
> Eric

Received on Wed Aug 12 1998 - 00:00:00 CDT