trigger to delete inserted row
253115Mar 17 2010 — edited Mar 18 2010Hi,
I'm novice in writing triggers. I'm trying to create a trigger that would delete a certain value as soon as it gets inserted. For example, if form99 is inserted, it would get deleted immediately. I have to do a trigger as a constraint will not work for our software.
I've tried this:
CREATE OR REPLACE TRIGGER trig_test
AFTER INSERT ON mytable
FOR EACH ROW
when (new.col1 in ('form99'))
BEGIN
DELETE from mytable WHERE col1 = 'form99' ;
commit;
END ;
/
Then inserted a row and got the mutating error.
"ORA-04091: table mytable is mutating, trigger/function may not see it"
I've searched around and found a suggestion: to avoid mutating, add a pragma autonomous_transaction and a commit :
CREATE OR REPLACE TRIGGER trig_test
before INSERT ON mytable
FOR EACH ROW
declare
pragma autonomous_transaction;
BEGIN
DELETE from mytable WHERE col1 = 'form99' ;
commit;
END ;
/
but this does not work. nothing gets deleted.
I've read about mutating triggers and understand that you can't do DML against the same table the trigger is on. I don't know what to do. I can write it so that it renames the form instead of deleting it. this worked but not for all cases.
Could someone please help me .
thank you.
Edited by: dkdc on Mar 17, 2010 1:01 PM