Archiving Deleted Records

[maxhugen]

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?

[PSoni]

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

[maxhugen]

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?

[vinit]

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

[Raminder]

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.

[tycho]

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

[maxhugen]

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

[jmodic]

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.