Re: Help with Trigger!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Mar 1999 15:24:11 GMT
Message-ID: <36e88e8f.9434185@192.86.155.100>

A copy of this was sent to "Database Generation Ltd." <datagen_at_dircon.co.uk> (if that email address didn't require changing) On Tue, 09 Mar 1999 19:25:34 +0000, you wrote:

>Dear friends,
>
>Is it possible (and if it possible - how) to create the trigger BEFORE
>delete that will copy the record before delete it into another table
>with the same structure?
>
>Thank you very much in advance.
>Eugene

It would look like this:

SQL> create table dept_del as
  2 select dept.*, sysdate timestamp from dept where 1=0;

Table created.

SQL>
SQL> create or replace trigger dept_trigger   2 before delete on dept
  3 for each row
  4 begin

  5          insert into dept_del( deptno, dname, loc, timestamp )
  6          values ( :old.deptno, :old.dname, :old.loc, SYSDATE );

  7 end;
  8 /

Trigger created.

SQL>
SQL> select * from dept;

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept_del;

no rows selected

SQL>
SQL> delete from dept;

4 rows deleted.

SQL> 
SQL> 
SQL> select * from dept;

no rows selected

SQL> select * from dept_del;

    DEPTNO DNAME LOC TIMESTAMP

---------- -------------- ------------- ---------
        10 ACCOUNTING     NEW YORK      10-MAR-99
        20 RESEARCH       DALLAS        10-MAR-99
        30 SALES          CHICAGO       10-MAR-99
        40 OPERATIONS     BOSTON        10-MAR-99



 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 10 1999 - 09:24:11 CST