-
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
-
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
-
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
-
That is quite common. Try a statement level trigger instead of a row level trigger.
Vinit
-
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 (
ld.col1,
ld.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
Max Hugen
Hugen Enterprises Pty Ltd
www.hugen.com.au
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|