Use of Trigger After a Transaction

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?

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:

CREATE TABLE Foo (F INT)
CREATE TABLE Bar (B INT)
INSERT INTO Foo VALUeS (1)
INSERT INTO Foo VALUeS (2)
INSERT INTO Bar VALUeS (100)

CREATE TRIGGER Tri ON Foo
FOR UPDATE
AS
DECLARE @BarVal INT
SELECT @BarVal = B FROM Bar

In this case, if you modify Bar before Foo in the same transaction, then changes in Bar by your own transaction will be visible to Tri on Foo. Example:

BEGIN TRAN
  UPDATE Bar 
  SET B = 200

  /* Prints 200 */
  UPDATE Foo
  SET F = F + 1
  ROLLBACK
BEGIN TRAN

And:

BEGIN TRAN
  /* Prints 100 */
  UPDATE Foo
  SET F = F + 1

  UPDATE Bar 
  SET B = 200
ROLLBACK TRAN

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 inserted and deleted documented here (old article, but still applies):

http://technet.microsoft.com/en-us/library/aa214435(v=sql.80).aspx

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.

StackExchange Database Administrators Q#71786, answer score: 2