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.

Member Avatar

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.