Oracle : Triggers inside a transaction (2 statements)?

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

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 be (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 be 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 be 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 be 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.

Thanks for your test case. The picture of the structure is close to ours (even more complicated of course). I tried it and it worked.So indeed, there's something else.

– Ashygan Jun 29 at 12:44.

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.

Table B doesn't have a date column (although it's planned in a near future and it won't be indexed). Instead, I have a primary key on table B whose value is taken from a sequence using a trigger on table B (before insert, for each row). Might it come from the second trigger?

Another info : I'm using bernate to access the database (insert, update, delete). I know that when Oracle fires an error coming from a trigger, I have an exception in my java code. Though, I didn't see an exception coming from Oracle in this case.

– Ashygan Jun 29 at 12:48 One of the triggers could be squashing an exception so bernate wouldn't have a chance to see it, but that's just a guess. It might be helpful to add the table structures and trigger code to the question. – Alex Poole Jun 29 at 13:06.

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).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.