Archiving Deleted Records

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?

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, :old.column1,:old.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

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?

That is quite common. Try a statement level trigger instead of a row level trigger.

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.

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 (
:old.col1,
:old.col2,
sysdate);
end;
/

HTH

Tycho

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

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.