ORA-04091: table [blah] is mutating, trigger/function may not see it

Solution 1:

I think I disagree with your description of what the trigger is trying to do. It looks to me like it is meant to enforce this business rule: For a given value of t1_appnt_event, only one row can have a non-NULL value of t1_prnt_t1_pk at a time. (It doesn't matter if they have the same value in the second column or not.)

Interestingly, it is defined for UPDATE OF t1_appnt_event but not for the other column, so I think someone could break the rule by updating the second column, unless there is a separate trigger for that column.

There might be a way you could create a function-based index that enforces this rule so you can get rid of the trigger entirely. I came up with one way but it requires some assumptions:

• The table has a numeric primary key
• The primary key and the t1_prnt_t1_pk are both always positive numbers

If these assumptions are true, you could create a function like this:

dev> create or replace function f( a number, b number ) return number deterministic as
  2  begin
  3    if a is null then return 0-b; else return a; end if;
  4  end;

and an index like this:

CREATE UNIQUE INDEX my_index ON my_table
  ( t1_appnt_event, f( t1_prnt_t1_pk, primary_key_column) );

So rows where the PMNT column is NULL would appear in the index with the inverse of the primary key as the second value, so they would never conflict with each other. Rows where it is not NULL would use the actual (positive) value of the column. The only way you could get a constraint violation would be if two rows had the same non-NULL values in both columns.

This is perhaps overly "clever", but it might help you get around your problem.

Update from Paul Tomblin: I went with the update to the original idea that igor put in the comments:

 CREATE UNIQUE INDEX cappec_ccip_uniq_idx 
 ON tbl1 (t1_appnt_event, 
    CASE WHEN t1_prnt_t1_pk IS NOT NULL THEN 1 ELSE t1_pk END);