Oracle Database on Rollback Trigger
I want to create a trigger that is executed when a table is updated.
in particular when updating a table. I want to update another table using a trigger, but if the trigger fails (REFERENTIAL INTEGRITY - ENTITY INTEGRITY), I no longer want to update.
Any suggestion on how to do this?
Is it better to use a trigger or do it anagrammatically with a stored procedure?
thanks
a source to share
The DML in the trigger is part of the same action as the initiating DML. Both must succeed or fail. If the trigger raises an unhandled exception, the entire statement is returned back.
Here is a trigger on T23 that copies a string to T42.
SQL> create or replace trigger t23_trg
2 before insert or update on t23 for each row
3 begin
4 insert into t42 values (:new.id, :new.col1);
5 end;
6 /
Trigger created.
SQL>
Successful invasion of T23 ...
SQL> insert into t23 values (1, 'ABC')
2 /
1 row created.
SQL> select * from t42
2 /
ID COL
---------- ---
1 ABC
SQL>
But this will fail due to the unique constraint on T42.ID. As you can see, the run instruction is rolled back as well ...
SQL> insert into t23 values (1, 'XYZ')
2 /
insert into t23 values (1, 'XYZ')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T24_PK) violated
ORA-06512: at "APC.T23_TRG", line 2
ORA-04088: error during execution of trigger 'APC.T23_TRG'
SQL> select * from t42
2 /
ID COL
---------- ---
1 ABC
SQL> select * from t23
2 /
ID COL
---------- ---
1 ABC
SQL>
a source to share
If the trigger fails, it will throw an exception (unless you tell it not to), in which case you will have a client rollback. It doesn't really matter if it is done with a trigger or SP (although it is often a good idea to keep the logical transaction inside the SP, rather than propagate it around triggers).
a source to share