Trigger to delete the inserted record.

[Zargo]

Hi all,

I want to create a trigger when i'm getting an insert record when recipient_name = 'TEST', i only want to delete this record if the doc_id exists more then one or equals to 1. I don't know if i have write this correctly, because i'm getting a ORA-04091: table is mutating, trigger/function may not see it, somebody idea's??

Here an example:

CREATE OR REPLACE TRIGGER UPDATE_FLOW
AFTER INSERT OR UPDATE
ON FLOW_CURRENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE VDOCID VARCHAR2(10)



BEGIN
IF :NEW.RECIPIENT_NAME = 'TEST' THEN
SELECT COUNTNEW.DOC_ID) INTO VDOCID FROM FLOW_CURRENT;
ELSEIF v_DOCID => 1 THEN
DELETE FROM FLOW_cURRENT WHERE DOC_ID = :NEW.DOC_ID AND :NEW.RECIPIENT_NAME = 'TEST' ;
END IF;


END IF;
END;
/

 

[sem]

General idea of resolving mutating trigger issue is to place identifiers (PK values) of updated/inserted rows into some temporary storage (collection packade variable or temporary table) and then process them row-by-row in post-command statement level trigger. Of course some kind of semaphor should be set up to avoid recursions and process each statement only once. This is quite easy.

Regards, Dima

http://seminihin.narod.ru