I have a scenario where there is a transaction being executed to update a few tables. On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on one of the tables used in the transaction. The question is: Will all the data from all of the tables in the transaction be available when the trigger executes?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
It depends on when the trigger fires during the transaction.
The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger.
To take a simple example, consider this data model:
In this case, if you modify
Within the trigger code, you can see both the previous and the new value of the table the trigger fired on by using the special tables
When you have triggers firing triggers, this very quickly becomes complicated. Generally, it is advisable to wrap complex logic operating on several tables in a stored procedure instead. That way you know that all the operations happen in the right order.