Oracle database on trigger fail rollback

Question

Hi

I want to create a trigger that execute on update of a table.

in particular on update of a table i want to update another table via a trigger but if the trigger fails (REFERENTIAL INTEGRITY-- ENTITY INTEGRITY) i do not want to execute the update anymore.

Any suggestion on how to perform this?

Is it better to use a trigger or do it anagrammatically via a stored procedure?

Thanks

Answer 1

If the trigger fails, it will raise an exception ( unless you specifically tell it not to ), in which case, you would have the client rollback. It doesn't really matter if its done via a trigger or a SP ( although its often a good idea to keep a logical transaction within a SP, rather than spread it around triggers ).

Answer 2

The DML in the trigger is part of the same action as the triggering DML. Both have to succeed or b oth fail. If the trigger raises an unhandled exception the entire statement gets rolled back.

Here is a trigger on T23 which copies the row into 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>

A successful inserrt into T23...

SQL> insert into t23 values (1, 'ABC')
  2  /

1 row created.

SQL> select * from t42
  2  /

        ID COL
---------- ---
         1 ABC

SQL>

But this one will fail because of a unique constraint on T42.ID. As you can see the triggering statement is rolled back too ...

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>