Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 452 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by montjoile
Member Avatar for 1stDAN

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

Edited by 1stDAN
Member Avatar for montjoile

ok thanks!

Be a part of the DaniWeb community

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