is it possible to cancel transaction in a trigger?

[montjoile]

hi

I had a question in an exam that asked for the code of a trigger that cancels a transaction if a query result doesn't meet a condition in oracle database.
is it possible? I am googleing but I can't find an answer
thanks

[pritaeas]

I found this, basically it triggers an application error.

No, a transaction can neither be started nor commited nor rollbacked from within a trigger fired from a DB action (insert, update, delete). This is ANSI standard PSM/SQL. Even propritary MySQL does not allow triggers to manipulate transactions.

However, if an error occurs in a trigger the complete SQL statement, which has fired the trigger including all DB-changes made by this trigger, fails. Thus a rollback will be automatically carried out, assuming that "autocommit on" has been disabled. (This behavior might be a brute-force method to cancel a transaction within a trigger.)

[montjoile]

ok thanks!