Trigger doesn't work(ORA-04088 error during execution of trigger)

Question

I have DB of selling tickets for trains. I have table Ticket and table Cancel(here are canceled tickets). I have already written a trigger, when if I add some ticket to table Cancel then this ticket sets free in table Ticket(Client_id attribute = NULL).

But I want to write new one: "if I have canceled ticket(Client_id = NULL in table Ticket), and then when I update ticket and sets Client_id = 'some_number'(e.g. Client_id = 5), then I should delete this ticket from Cancel table. Here is my trigger:

CREATE OR REPLACE TRIGGER Buy_Cancel
  AFTER UPDATE
  ON TICKET
  FOR EACH ROW
  WHEN(old.CLIENT_ID IS NULL)
DECLARE 
  rowAmmount INTEGER;
BEGIN 
  SELECT COUNT(*)
    INTO rowAmmount
    FROM CANCEL c JOIN TICKET t ON c.TICKET_ID = t.TICKET_ID
    WHERE t.TICKET_ID = :OLD.TICKET_ID;
  IF rowAmmount > 0 THEN 
    DELETE FROM CANCEL c
      WHERE c.TICKET_ID = :NEW.TICKET_ID;
  END IF;
END;

When I run a query:

UPDATE TICKET t
  SET t.CLIENT_ID = 5
  WHERE t.TICKET_ID = 2;

it cause such errors:

ORA-04091: table MYDB.TICKET is mutating, trigger/function may not see it
ORA-06512: at "MYDB.BUY_CANCEL", line 4
ORA-04088: error during execution of trigger 'MYDB.BUY_CANCEL' SQL6.sql 2 8

Not for nothing, but "CANCEL" is not a good name for a database table. In any case, see [this question](http://stackoverflow.com/questions/2996748/mutating-trigger-function-may-not-see-it-error-during-execution-of-trigger?rq=1) which may provide some insight into the error message you're receiving. — sgress454, Mar 21 '14 at 23:34
I deleted my second trigger, so this is the only trigger in my DB. But the errors stay the same — user3444106, Mar 22 '14 at 07:55

score 0 · Accepted Answer · answered Mar 22 '14 at 11:31

Not sure I quite understand your logic, but the error is because you're querying the ticket table within a trigger on that table. You don't need to - the join doesn't seem to be adding anything, since you already have the ticket ID:

CREATE OR REPLACE TRIGGER Buy_Cancel
  AFTER UPDATE
  ON TICKET
  FOR EACH ROW
  WHEN(old.CLIENT_ID IS NULL)
DECLARE 
  rowAmmount INTEGER;
BEGIN 
  SELECT COUNT(*)
    INTO rowAmmount
    FROM CANCEL c
    WHERE c.TICKET_ID = :OLD.TICKET_ID;
  IF rowAmmount > 0 THEN 
    DELETE FROM CANCEL c
      WHERE c.TICKET_ID = :NEW.TICKET_ID;
  END IF;
END;

There isn't much point doing the query at all though, really; just dpong the delete on its own. would work - it won't error if there are no matching rows, so it would simplify it quite a bit. And you're using :old in the select, and :new in the delete; that's fine if they're the same (which is likely if that's the primary key), but looks odd anyway.

Yes, this works. I just didn't know that I can't query Ticket table within a trigger on that table. As I understand, I can't query the table, because I change data in that table? I admit that it's odd to use :OLD and then :NEW, but in my case there is no difference, because i'm changing only Client_id, not Ticket_id. Just forgot to write in trigger update OF CLIENT_ID; — user3444106, Mar 22 '14 at 16:48

Trigger doesn't work(ORA-04088 error during execution of trigger)

1 Answers1