Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: On Delete Trigger

    Hi all

    I want to write a on delete trigger which on deletion of the particular entries should insert those values to be deleted first into another table and then delete the record. Is it possible. Please let me know @ the earliest.

    Regards
    Dinesh

  2. #2
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    Yes you can. I wonder why you want to however. You may want to consider having a delete_yn flag and leaving them where they are -- i.e. performing a logical delete ???.


    Here is a simple example of what you requested:


    -- Using the scott/tiger demo schema

    -- NOTE: If you don't have the scott/tiger demo schema, then create one:
    -- 1. Create a scott/tiger user
    -- 2. connect as scott/tiger
    -- 3. run $ORACLE_HOME/sqlplus/demo/demobld.sql

    -- Create a table to hold the deleted dept records

    create table dept_del as
    select * from dept where 1 = 2;

    -- Create a delete trigger on dept

    create or replace trigger dept_del_trig
    before delete on dept
    for each row
    declare
    dummy integer;
    begin
    insert into dept_del (deptno,dname,loc)
    values (ld.deptno, ld.dname, ld.loc);
    end;
    /

    -- Now delete a row

    delete dept where deptno = 40;
    commit;

    select * from dept;

    select * from dept_del;

    Trigger created.

    SQL>
    SQL>
    SQL> -- Now delete a row
    SQL>
    SQL> delete dept where deptno = 40;

    1 row deleted.

    SQL> commit;

    Commit complete.

    SQL>
    SQL> select * from dept;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO

    SQL>
    SQL> select * from dept_del;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    40 OPERATIONS BOSTON

    SQL>

    Best Wishes,
    Troy

  3. #3
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    The funny faces where supposed to be COLON + "old"

  4. #4
    Join Date
    May 2002
    Posts
    29
    Hi,

    Sorry to bother u again. This trigger works fine when both table a and b have the same structure. I have a small change in the table b which is the timestamp field in addition to the existing fields of table a to capture the time @ which the record was deleted. Then I have a problem using the code. Can u please help me out.

    Reg
    Dinesh

  5. #5
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    Remeber, all the trigger is doing is using the old data fields as insert values into a target table. Create the target table any way you want, and modify the insert to record whatever you want. For example:

    alter table dept_del add (deleted_dt date);

    create or replace trigger dept_del_trig
    before delete on dept
    for each row
    declare
    dummy integer;
    begin
    insert into dept_del (deptno,dname,loc, deleted_dt)
    values (:old.deptno, :old.dname, :old.loc, sysdate);
    end;
    /


    delete dept where deptno = 10;
    commit;


    select * from dept;

    -- Alter your session so that the date format shows full date + time
    alter session set nls_date_format = 'RRRR/DD/YY HH24:MI:SS';

    select * from dept_del;


    Best Wishes,
    Troy

  6. #6
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    BTW, here is the output of my example commands:

    SQL> alter table dept_del add (deleted_dt date);

    Table altered.

    SQL> -- Now delete a row
    SQL>
    SQL> delete dept where deptno = 10;

    1 row deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from dept_del;

    DEPTNO DNAME LOC DELETED_D
    ---------- -------------- ------------- ---------
    40 OPERATIONS BOSTON
    10 ACCOUNTING NEW YORK 24-MAY-02

    SQL> -- Alter your session so that the date format shows full date + time
    SQL> alter session set nls_date_format = 'RRRR/DD/YY HH24:MI:SS';

    Session altered.

    SQL> select * from dept_del;

    DEPTNO DNAME LOC DELETED_DT
    ---------- -------------- ------------- -------------------
    40 OPERATIONS BOSTON
    10 ACCOUNTING NEW YORK 2002/24/02 07:51:37

  7. #7
    Join Date
    May 2002
    Posts
    29

    Thankz a lot dude

    Troy,

    U have been real helpfull yaar (Buddy in Hindi). Thankz a lot once again.

    Regards
    Dinesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •