Oracle Trigger (after/Before Update) with exception-CodePudding

I've a quick question that looks basic, but I'd like to be sure before giving an answer to my work fellows.

In Oracle PL/SQL trigger, when an exception is fired within an "After Update" trigger, is the row being updated will be rolled back because the fact of "After update and not Before" behaviour ?

Or where the trigger timing ( before or after), the trigger will be reverted at any expcetion ?

Thank you in advance !

CodePudding user response：

The documentation says:

In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.
...

It's easy to test what happens, with a row-level trigger:

create trigger t42_trig
after update on t42
for each row
begin
  raise_application_error(-20000, 'Nope');
end;
/

update t42 set value = 'Updated' where id = 42;

ORA-20000: Nope
ORA-06512: at "MY_SCHEMA.T42_TRIG", line 2
ORA-04088: error during execution of trigger 'FIDDLE_IIIJPEHAHWSVOWUBIPKE.T42_TRIG'

select * from t42;

ID	VALUE
42	Original

... or with a statement-level trigger;

create trigger t42_trig
after update on t42
begin
  raise_application_error(-20000, 'Nope');
end;
/

update t42 set value = 'Updated' where id = 42;

ORA-20000: Nope
ORA-06512: at "MY_SCHEMA.T42_TRIG", line 2
ORA-04088: error during execution of trigger 'FIDDLE_IIIJPEHAHWSVOWUBIPKE.T42_TRIG'

select * from t42;

ID	VALUE
42	Original

fiddle

CodePudding user response：

That's right, and it was the behavior I've had ( always the original value remains stored) when run my test like yours. I'd just like to be sure about that, and I think I've got the answer I'm looking for. Thanks @Alex for your quick reply.