Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
When you say: "when the trigger executes" do you mean from within the trigger code? –  Thomas Kejser Jul 17 at 21:11
    
Yes. There is a read trigger that runs on one of the tables –  Mark McWhirter Jul 17 at 23:02
    
I added a response This is a great question by the way, I wish I could have added some of my rep as a reward for asking it. –  Thomas Kejser Jul 18 at 9:13

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for the clear explanation! –  Mark McWhirter Jul 22 at 23:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.