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