3

i'm using mysql db & java, so in my application i need to use trigger, but i need to make it save while inserting or updating. when insering new record in table A, trigger will do the work to insert some information in another table B. also when doing update for records in table A, trigger will update records for table B.

if transaction during update or insrt rolledback, does this rolled back any changes done by the trigger.??!!

Motasem
  • 599
  • 1
  • 6
  • 13

1 Answers1

1

Yes, if the transaction is rolled back, the work done by the trigger will also be rolled back (unless you do some monkeying with transaction scoping inside the trigger to specifically prevent this)

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • and how to do that monkeying in order to prevent work done by trigger to be rolled back? – Matija Nalis Apr 30 '19 at 00:35
  • @MatijaNalis - good question, it actually looks like you can't do nested transactions in MySQL, but you might be able to accomplish something similar with savepoints if you really wanted to. I'm more curious why you would want to though - the use cases for doing that are pretty limited in my experience. – Eric Petroelje May 02 '19 at 18:03
  • I'm trying to keep [some logs](https://dba.stackexchange.com/q/236982/53319) about attempted (both successful and failed) transactions. Without being able to preserve data across rollback, I could only log about successful transactions. – Matija Nalis May 03 '19 at 10:12
  • @MatijaNalis - Yup, that's the one use case I've ever needed this functionality for too. In my case I was using C# and DTC on MsSQL server, but it looks like that functionality may not be available in MySQL. – Eric Petroelje May 03 '19 at 18:21