Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    27

    Unanswered: Trigger to back up data

    Hi,

    What I am trying to do is create a trigger that will back up of all rows data that are deleted. Here's one of many that doesn't work:

    CREATE OR REPLACE
    ARCHIVE_emp BEFORE DELETE ON emp FOR EACH ROW
    AS

    BEGIN
    INSERT INTO bck_up_emp select old.* from emp;
    END;

    From reading up, it seems that you can't do a select in a trigger from mutating tables. Surely there must be a good way of doing this.

    What I don't want to do is have to refence all the field names in the table in turn.

    Any help would be gratefully received.

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    CREATE OR REPLACE
    ARCHIVE_emp BEFORE DELETE ON emp FOR EACH ROW
    AS

    BEGIN
    INSERT INTO bck_up_emp values (
    col1,
    col2,
    col3
    .....);
    END;

    I assume the two tables are the same ? (same num & type of columns)

    I think this should work.

    Rgs,
    Breen.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    another workaround is to create a procedure with the pragma autonomous transaction, In this procedure you can use the select statement to do the insert.
    But the other solution works fine too.

    Good luck.
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger to back up data

    It is hard to avoid referencing all the column names.

    The way to work around the mutating table problem is to move your DML into a statement level AFTER trigger. But by the time that trigger fires, your records will have been deleted so you can't SELECT them!

    The normal way to get around that is to copy all the OLD values into arrays in a package in a FOR EACH ROW trigger. But that means referencing each individual column again...

    Another attack is to use PRAGMA AUTONOMOUS_TRANSACTION in your FOR EACH ROW trigger. This overcomes the mutating table problem BUT will potentially corrupt your database - if the user issues a ROLLBACK, the DELETE will be undone but the INSERT into the audit table will not be.

    Now, I believe the following might work, but it uses a feature of O9i and as I only have access to O8i right now, I can't verify it. It uses the standard mutating table workarounds described above, with a twist to avoid referencing column names (this is the O9i bit I can't test right now).

    First, we need a package to remember all the OLD records:

    Code:
    create or replace package emp_trg_pkg as
      type emp_type is table of emp%ROWTYPE index by binary_integer;
      emp_tab emp_type;
    end;
    Now a before statement trigger to ensure it is empty:

    Code:
    create or replace trigger emp_bds before delete on emp
    begin
      emp_trg_pkg.emp_tab.delete;
    end;
    Now an after row trigger to track the values:

    Code:
    create or replace trigger emp_adr after delete on emp
    for each row
    declare
      -- To allow us to select the old values
      pragma autonomous_transaction;
    begin
      select *
      into   emp_trg_pkg.emp_tab(emp_trg_pkg.emp_tab.COUNT+1)
      from   emp
      where  empno = :old.empno;
    end;
    Finally an after statement trigger to insert the saved data into the backup table:

    Code:
    create or replace trigger emp_ads after delete on emp
    begin
      for i in 1..emp_trg_pkg.emp_tab.COUNT loop
        insert into emp_backup values emp_trg_pkg.emp_tab(i);
      end loop;
    end;
    This is the bit I can't do on O8i! In O8i I have to reference the columns like this:

    Code:
    create or replace trigger emp_ads after delete on emp
    begin
      for i in 1..emp_trg_pkg.emp_tab.COUNT loop
        insert into emp_backup (empno, ename) values (emp_trg_pkg.emp_tab(i).empno, emp_trg_pkg.emp_tab(i).ename);
      end loop;
    end;
    ... which of course defeats the object.

    You may find it easier just to type in the pesky column names!

Posting Permissions

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