Archiving Deleted Records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Archiving Deleted Records

  1. #1
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Question

    I would like to archive any records which have been deleted to a backup table. I tried using a trigger to do this:

    CREATE OR REPLACE TRIGGER archiveClient
    BEFORE DELETE ON Client
    FOR EACH ROW
    BEGIN
    INSERT INTO zClient
    SELECT sysdate, Client.*
    FROM Client;
    COMMIT;
    END archiveClient;

    Here, the backup table zClient has the same fields as Client, but without any constraints. There is one extra field, DateDeleted, which is filled by sysdate as above.

    Unfortunately, I am getting a mutating table error, ORA-06512. Can anyone please suggest the correct way to do this?
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  2. #2
    Join Date
    Feb 2001
    Posts
    125
    Hi
    Try this

    CREATE OR REPLACE TRIGGER archiveClient
    BEFORE DELETE ON Client
    FOR EACH ROW
    referencing new as new old as old
    BEGIN
    INSERT INTO zClient values (sysdate, ld.column1,ld.column2,....)
    END archiveClient;

    -----mutating error
    this is because u r using Select statement where the table is in transition state(delete is going on)


    Thanks
    Pitamber Soni

  3. #3
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    Thanks

    However, my trigger seemed to compile ok, I don't think that's the problem.... how do I work around the mutating table problem?

    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    That is quite common. Try a statement level trigger instead of a row level trigger.

    Vinit

  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Even if it is compiling OK, it will give the mutating error. You will not achieve your objective of archiving deleted records unless you reference old (record) with a keyword and insert it into the zclient table as explained by Soni.
    My advise is create trigger as given by PSoni and then see if objective is met.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  6. #6
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Smile

    Hi,

    This is how archiving of deleted data works around here.

    CREATE OR REPLACE TRIGGER del_trg
    after delete on prod.tbl
    referencing old as old
    for each row
    declare
    sqlerr number;
    begin
    insert into arc.tbl (
    col1,
    col2,
    del_dt)
    values (
    ld.col1,
    ld.col2,
    sysdate);
    end;
    /

    HTH

    Tycho

  7. #7
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Unhappy

    Hello all, thanks for your comments to date... but I am still doing something wrong? My trigger is:

    CREATE OR REPLACE TRIGGER "GSUSER"."XBENEFIT" BEFORE DELETE ON "GSUSER"."BENEFIT"
    REFERENCING OLD AS old NEW AS new
    FOR EACH ROW BEGIN
    INSERT INTO xBenefit (
    Benefit,
    SortOrder,
    CreatedUser,
    CreatedDT,
    UpdatedUser,
    UpdatedDT
    )
    VALUES (
    old.Benefit,
    old.SortOrder,
    old.CreatedUser,
    old.CreatedDT,
    old.UpdatedUser,
    old.UpdatedDT
    );
    COMMIT;
    END xClient;

    I'm getting a compile error on this... "Identifier 'OLD.ENEFIT' must be declared." Could anyone suggest what I've done wrong please?

    MTIA
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Use semicolon before the OLD.column_name, like

    ....
    VALUES (
    :OLD.Benefit,
    :OLD.SortOrder,
    ....)

    And don't use COMMIT inside your trigger. COMMIT and ROLLBACK can't be used in the trigger, unless you use AUTONOMUS_TRANSACTION pagma.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width