Mutating table on Oracle SQL trigger

Question

I'm trying to do a trigger but I get a mutating table error. The SQL code is something like this:

CREATE OR REPLACE TRIGGER CHK_Apartado_D
BEFORE INSERT OR UPDATE ON CONTRACTS
FOR EACH ROW
DECLARE 
errorvisualizacion EXCEPTION;
local_enddate DATE;
BEGIN
  SELECT enddate INTO local_enddate FROM CONTRACTS WHERE clientid=:new.clientid;
  IF local_enddate > SYSDATE OR local_enddate IS NULL
  THEN 
    UPDATE CONTRACTS SET enddate = SYSDATE - 1 WHERE clientid=:new.clientid;
  END IF;
END CHK_Apartado_B;
/

And the error that I get is this:

Informe de error -
Error SQL: ORA-04091: table HR.CONTRACTS is mutating, trigger/function may not see it
ORA-06512: at "HR.CHK_APARTADO_D", line 5
ORA-04088: error during execution of trigger 'HR.CHK_APARTADO_D'
ORA-06512: at "HR.CHK_APARTADO_D", line 8
ORA-04088: error during execution of trigger 'HR.CHK_APARTADO_D'
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.

When I INSERT a new contract I have to check if that client have an other contract in actual date, and if he have I must update de end date contracte to yesterday and let the new INSERT. So, how can I do to prevent the mutating table?

This seems to be an exercise that is confusing a few people recently:. [here](http://stackoverflow.com/q/43494370/266304), [here](http://stackoverflow.com/q/43483253/266304), [here](http://stackoverflow.com/q/43238488/266304); oh, and this time last year too, [here](http://stackoverflow.com/q/36372988/266304),... — Alex Poole, Apr 20 '17 at 16:29
@AlexPoole - Seems like we need a canonical answer and just keep closing on duplicate. — APC, Apr 20 '17 at 17:01
@APC - is a canonical answer that will effectively be the answer to the assignment appropriate though? Although I'm not sure I've seen the assignment stated anywhere in a way that suggests what answer the assigner is actually looking for... or at least not an answer I'd be happy to try to write *8-) — Alex Poole, Apr 20 '17 at 17:17

score 0 · Answer 1 · answered Apr 20 '17 at 13:53

A common solution is Using Compound Triggers to Avoid Mutating-Table Error.

Better still, don't attempt to use triggers to implement complex business logic. Instead, build an API package with a procedure insert_contract that implements the business rules, and ensure (via privileges) that users call that API rather than inserting directly into the table. Triggers can get very messy, very quickly.

score 0 · Accepted Answer · answered Apr 20 '17 at 13:57

0

Your trigger fires on update or insert of CONTRACTS and then tries to update CONTRACTS, which fires the trigger.... See the problem?

You need to work out the enddate and then actually do the insert / update.

answered Apr 20 '17 at 13:57

OTTA

1,071
7
8

Mutating table on Oracle SQL trigger

2 Answers2

Linked

Related