0

I have a trigger like this: (Basically on update of a column in table1, I update a column in table 2)

CREATE OR REPLACE TRIGGER AAA   AFTER UPDATE 
   ON TABLE_1    REFERENCING NEW AS NEWROW OLD AS OLDROW
   FOR EACH ROW
WHEN (
NEWROW.DELETED ='Y' AND NEWROW.ID IN (41,43)
AND OLDROW.DELETED = 'N'
      )
DECLARE
id_1 number;
id_2 number;
id_3 number;
BEGIN

select id_1, id_2,id_3 into id_1,id_2,id_3  from  table_1 where id_1 = :NEWROW.id1 and id2 = some_other_row.id2;
if id_1 is  null
then

 update table2 set deleted = 'Y' ,   where table2.id_1 = id_1 and table2.id_2=id_2 and table2.id_3 = id_3;

   end if;

EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END AAA;
/

When I update table1 I get:

ORA-04091: table table1 is mutating, trigger/function may not see it

I thought this error happens only when you are updating the table on which the trigger is trying to update something. But here I am updating table1 and trigger is supposed to update table2. SO why is the error?

Victor
  • 16,609
  • 71
  • 229
  • 409
  • 1
    Do you mean AFTER UPDATE _OR_ DELETE? Your variable `id3` is incorrect and I don't understand why you're checking whether `id_1` is null. Can you explain your table structure and _exactly_ what you're trying to achieve here. I assume there's significantly more to the trigger that you've left out? Maybe you could create a minimal _working_ example that demonstrates your problem? – Ben May 28 '13 at 20:48
  • 1
    I think I'd combine the select from table_1 with the update as a single statement, but I believe it's the select from table_1 that's causing the mutating table error. – David Aldridge May 28 '13 at 21:22

1 Answers1

1

It's the SELECT statement that is causing the problem here. Inside the trigger, you cannot SELECT from the same table. In your example, you don't need/can't use the SELECT statement. You can get the values by simply using :newrow.id_1, :newrow.id_2 and :newrow.id_3.

Noel
  • 10,152
  • 30
  • 45
  • 67
  • Actually, I modified the select statement to demonstrate why i need it. Can you check please? To use the select, I was using pragma autonomous_transaction; Now the problem is that when i update the table, the trigger will fire before the initial update is committed and that is not what i want. i want the trigger to fire after the initial update is committed – Victor May 29 '13 at 20:39
  • Then don't use an autonomous transaction @Victor (ever - appart from error logging) and you need to update your question with the actual trigger you're using as yours is still incorrect. – Ben May 29 '13 at 20:47
  • But without autonomous transaction, how can i use the select clause on the same table on which the trigger is sitting? – Victor May 29 '13 at 20:58