Oracle : Triggers inside a transaction (2 statements)

I'm working on a Oracle 10g DB and I have a problem with my triggers inside a transaction. Table A has 3 triggers : one before insert, one before update and last before delete. Whenever a trigger is fired, it writes a line on table B, like some sort of log. All triggers are configured on "before xxx" and "on each row". Using single queries, I can see they're working.

But when I do two queries modifying the same line during one transaction, (typically, I modify a line and then I delete it, so an update and a delete), only the first trigger (update) is run. At the end of the transaction, I can see my line in table A has been deleted, but there's only one line in table B, showing the update action,开发者_StackOverflow社区 but not the delete one.

I may have misconfigured something. Is it something coming from the trigger configuration ?

---

There's no reason for the second trigger not to fire, there's something else going on.

Here's a little test case that shows that all triggers fire correctly when a single transaction modifies a single row several times:

SQL> CREATE TABLE a (ID NUMBER);

Table created

SQL> CREATE TABLE b (action VARCHAR2(3), id_old NUMBER, id_new NUMBER);

Table created

SQL> CREATE TRIGGER trg_a_ins BEFORE INSERT ON a FOR EACH ROW
  2  BEGIN
  3     INSERT INTO b VALUES ('ins', :old.id, :new.id);
  4  END;
  5  /

Trigger created

SQL> CREATE TRIGGER trg_a_upd BEFORE UPDATE ON a FOR EACH ROW
  2  BEGIN
  3     INSERT INTO b VALUES ('upd', :old.id, :new.id);
  4  END;
  5  /

Trigger created

SQL> CREATE TRIGGER trg_a_del BEFORE DELETE ON a FOR EACH ROW
  2  BEGIN
  3     INSERT INTO b VALUES ('del', :old.id, :new.id);
  4  END;
  5  /

Trigger created

SQL> INSERT INTO a VALUES (1);

1 row inserted

SQL> UPDATE a SET ID = 2 WHERE ID = 1;

1 row updated

SQL> DELETE FROM a WHERE ID = 2;

1 row deleted

SQL> select * from b;

ACTION     ID_OLD     ID_NEW
------ ---------- ----------
ins                        1
upd             1          2
del             2 

---

As @Vincent Malgrat says, something else is going on. Does Table B have a date column with a unique index on it? First possibility that comes to mind is that the two records are inserted with the same date (audit) value, to the nearest second; an index clash causes an error on the second insert from the delete; but that is squashed inside the delete trigger. Rather speculative I know, and it would be less likely with a timestamp column, and would occasionally work as expected if the timing was just right.

---

It is possible (and desirable) for an ORM to 'compress' multiple changes to a single record. So the code may update three attributes and the ORM may have updated the record/instance multiple times in its cache, but only send a single UPDATE statement at the end of the transaction. Or, if the record ended up being deleted, it might skip any updates and just delete the original record.

That said, I can't explain why a record could be deleted without the DELETE trigger being fired. I assume you are using real deletes rather than a 'soft' delete (it marking a record as deleted and just hiding it from view).