CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Maddy
Maddy

Reputation: 3816

Mutating trigger issue in oracle

I am facing mutating trigger error,

I will describe the issue here I am using tableA and tableB.

TableA holds a column called empChecked which can hold 2 values '-', '+'.
TableB holds a column called mgrChecked which can hold 2 values '-', '+'.

The current requirement is both fields in empChecked and mgrchecked must be in sync. i mean if empChecked is updated to '+' then mgr checked in tableB must be updated to '+' and vice versa. Updation is possible from front end for both fields.

I have created trigger on both the tables. but i am facing ora-04091 error.

Please suggest me any approach to achieve this?

Upvotes: 1

Views: 925

Answers (3)

Passerby
Passerby

Reputation: 10070

You're a little unclear on detail, but I think this would help (assuming you have an ID column on both tables)

CREATE TRIGGER tri_table_a
    AFTER UPDATE ON table_a
DECLARE
    hits NUMBER :=0;
BEGIN
    SELECT count(*) INTO hits FROM table_a a INNER JOIN table_b b ON (a.id=b.id) WHERE a.emp_checked<>b.mgr_checked;
    IF hits>0 THEN
        UPDATE table_b b SET mgr_checked=(SELECT emp_checked FROM table_a a WHERE a.id=b.id);
    END IF;
END;
/

CREATE TRIGGER tri_table_b
    AFTER UPDATE ON table_b
DECLARE
    hits NUMBER :=0;
BEGIN
    SELECT count(*) INTO hits FROM table_a a INNER JOIN table_b b ON (a.id=b.id) WHERE a.emp_checked<>b.mgr_checked;
    IF hits>0 THEN
        UPDATE table_a a SET emp_checked=(SELECT mgr_checked FROM table_b b WHERE a.id=b.id);
    END IF;
END;
/

The key is don't use FOR EACH ROW, which will cause table lock.

Edit

  • This will NOT conflicts with table lock or ORA-04091, because there's no FOR EACH ROW in trigger condition (a.k.a. not a row trigger), which means the trigger body will only be executed AFTER the whole update action has been finished;

  • This will NOT cause an infinite loop, because when count(*) in trigger body returns 0, the UPDATE inside trigger body will not be executed;

  • Also, count(*) has to be used because this is a statement trigger and there's no :new.emp_checked available;

  • General scenario: Update table_a -> triggers tri_table_a -> [trigger]check count -> [trigger]update table_b ->triggers tri_table_b -> [trigger]check count -> done.

  • I tried this out myself before posting this answer.

Upvotes: -1

APC
APC

Reputation: 146199

The mutating table error is a code smell. It almost always points to a bad data model, usually insufficient normalisation.

Certainly a bad data model is in evidence here. You have a column on a table with two settings. That's fine. Now you want to add the same column to a second table and keep the two in sync. This new column is completely pointless. There is no information available in that new column than you cannot get from querying the first table.

And that's what ORA-04091 is telling you. You can spend an awful lot of time building a workaround but that would all be wasted effort.

Upvotes: 4

Nick Krasnov
Nick Krasnov

Reputation: 27251

Instead of using triggers for that kind of synchronization why don't you just create a view named TableB that will "contain" all data from TableB (not mgrchecked) + empChecked column from TableA Something like this

create or replace view TAbleB as
  select t1.id
       , t1.Column2
       , ... 
       , t1.ColumnN
       , t.empChecked
    from TableA t
       , TableB t1
   where t.id = t1.id

Upvotes: 1

Related Questions