CODEWITHSUNDEEP
oracle-database
gd1
gd1

Reputation: 11403

Creating an UPDATE trigger that causes the removal of the triggering row

I'm on Oracle Express 11g. I'm trying to create this trigger:

CREATE TRIGGER remove_useless_surveys 
     BEFORE UPDATE OF agent_id, agency_id, province_id ON surveys 
     FOR EACH ROW WHEN (
     new.agent_id IS NULL AND 
     new.agency_id IS NULL AND
     new.province_id IS NULL)
DELETE FROM surveys WHERE survey_code = :new.survey_code

agent_id, agency_id and province_id are foreign keys, with ON DELETE SET NULL clause.

I need a row in the surveys table to be DELETED when all the three foreign keys are set to NULL (because the referred rows are deleted).

The trigger compiles with no problems, but when the condition fires, then I get this error:

SQL error: ORA-04091: table REALESTATE.SURVEYS is mutating,
trigger/function may not see it ORA-06512: at
"REALESTATE.REMOVE_USELESS_SURVEYS", line 1 ORA-04088: error during
execution of trigger 'REALESTATE.REMOVE_USELESS_SURVEYS'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

Actually, I know I'm editing the same table that fired the trigger. But I want to cancel the update, or finalize it, I don't care, and then delete that row.

Can you help me? How can I achieve what I'm trying to do?

Upvotes: 2

Views: 682

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

You could use a statement level trigger like this:

CREATE TRIGGER remove_useless_surveys 
AFTER UPDATE OF agent_id, agency_id, province_id ON surveys 
BEGIN
  DELETE FROM surveys 
  WHERE  agent_id IS NULL 
  AND    agency_id IS NULL
  AND    province_id IS NULL;
END;

Upvotes: 3

Related Questions