Transactions and Triggers

SQL Server 2000 provides two types of triggers ” AFTER triggers and INSTEAD OF triggers. INSTEAD OF triggers perform their actions before and modifications are made to the actual table the trigger is defined on.

Whenever trigger is invoked, it is always invoked within another transaction, whether it's a single statement AutoCommit transaction, or a user -defined multistatement transaction. This is true for both AFTER triggers and INSTEAD OF triggers. Even though an INSTEAD OF trigger fires before, or "instead of" the data modification statement itself, if a transaction is not already active, an AutoCommit transaction is still automatically initiated as the data modification statement is invoked. (For more information on AFTER and INSTEAD OF triggers, see Chapter 29, "Creating and Managing Triggers.")

NOTE

While the information presented in this section applies to both AFTER and INSTEAD OF triggers, the primary focus and the examples presented pertain primarily to AFTER triggers.

Because the trigger will already be operating within the context of a transaction, the only transaction control statements you should ever consider using in a trigger are ROLLBACK and SAVE TRAN . You don't need to issue a BEGIN TRAN because a transaction is already active; a BEGIN TRAN would only serve to increase the nesting level, which would only complicate things further.

To demonstrate the relationship between a trigger and the transaction, use the following SQL code to create a trigger on the employee table:

 use pubs  go CREATE TRIGGER tD_employee ON employee FOR DELETE AS    DECLARE @msg VARCHAR(255)    SELECT @msg = 'Trancount in trigger = ' + CONVERT(VARCHAR(2), @@trancount)    PRINT @msg    RETURN go 

The purpose of this trigger is to simply show the state of the @@trancount within the trigger as the deletion is taking place.

If you now execute code for an implied and an explicit transaction, you can see the values of @@trancount and the behavior of the batch. First, here's the implied transaction:

 print 'Trancount before delete = ' + CONVERT(VARCHAR(2), @@trancount)  DELETE FROM employee WHERE emp_id = 'PMA42628M' print 'Trancount after delete = ' + CONVERT( VARCHAR(2), @@trancount) go 

The results of this are as follows :

 Trancount before delete = 0  Trancount in trigger = 1 (1 row(s) affected) Trancount after delete = 0 

Because no transaction starts until the DELETE statement executes, the first value of @@trancount indicates this with a value of . Within the trigger, the transaction count has a value of 1 ; you are now inside the implied transaction caused by the DELETE . After the trigger returns, the DELETE is automatically committed and the transaction is finished, and @@trancount returns to to indicate that no transaction is currently active.

Now explore what happens within an explicit transaction:

 begin tran  print 'Trancount before delete = ' + CONVERT(VARCHAR(2), @@trancount) DELETE FROM employee WHERE emp_id = 'PMA42628M' print 'Trancount after delete = ' + CONVERT( VARCHAR(2), @@trancount) commit tran print 'Trancount after commit = ' + CONVERT( VARCHAR(2), @@trancount) go 

This code gives the following results:

 Trancount before delete = 1  Trancount in trigger = 1 (0 row(s) affected) Trancount after delete = 1 Trancount after commit = 0 

In this example, a transaction is already active when the DELETE is executed. The BEGIN TRAN statement initiates the transaction and @@trancount is 1 before the DELETE is executed. The trigger becomes a part of that transaction, which is not committed until the COMMIT TRAN statement is executed.

What would happen, however, if the trigger performed a rollback? Now modify the trigger to perform a rollback as follows:

 ALTER TRIGGER tD_employee ON employee  FOR DELETE AS print 'Trancount in trigger = ' + CONVERT(VARCHAR(2), @@trancount) ROLLBACK TRAN return 

Now rerun the previous batch. The outcome this time is as follows:

 Trancount before delete = 1  Trancount in trigger = 1 

Notice in this example that the batch did not complete, as evidenced by the missing output from the last two print statements. When a rollback occurs within a trigger, SQL Server aborts the current transaction, continues processing the commands in the trigger, and after the trigger returns, aborts the rest of the batch. A ROLLBACK TRAN in a trigger will roll back all work to the first BEGIN TRAN statement. It is not possible to roll back to a specific named transaction, although you can roll back to a named savepoint, as will be discussed later in this section.

Again, the batch and transaction are finished when the trigger rolls back; any subsequent statements in the batch are not executed. The key concept to remember is that the trigger becomes an integral part of the statement that fired it and of the transaction in which that statement occurs.

Now look at another example. First, create a trigger to enforce referential integrity between the titles table and the publishers table:

 create trigger tr_titles_i on titles for insert as  declare @rows int  -- create variable to hold @@rowcount select @rows = @@rowcount if @rows = 0 return if update(pub_id) and (select count(*)         from inserted i, publishers p         where p.pub_id = i.pub_id ) != @rows   begin         rollback transaction         raiserror ('Invalid pub_id inserted', 16, 1)   end return go 

Next, for the trigger to take care of the referential integrity, you need to disable the foreign key constraint on the titles table:

 alter table titles nocheck constraint FK__titles__pub_id__014935CB 

Now, run a multistatement transaction with an invalid pub_id provided in the second insert statement:

 /* transaction inserts rows into a table */  begin tran add_titles insert titles (title_id, pub_id, title)        values ('BU1234', '0736', 'Tuning SQL Server') insert titles (title_id, pub_id, title)        values ('BU1235', 'abcd', 'Tuning SQL Server') insert titles (title_id, pub_id, title)        values ('BU1236', '0877', 'Tuning SQL Server') commit tran 

How many rows are inserted if 'abcd' is an invalid pub_id ? No rows will be inserted because the rollback tran in the trigger rolls back all modifications made by the trigger, including the insert with the bad pub_id and all statements preceding in the transaction. After the return statement is encountered in the trigger, it then aborts the rest of the batch. Remember to perform a return following a rollback tran in a trigger to prevent unwanted results. If the trigger subsequently performs data modifications before the return, they will no longer be part of the transaction and will be auto-committed with no opportunity to roll them back.

Never issue a BEGIN TRAN statement in a transaction because a transaction is already active at the time the trigger is executed. Rolling back to a named transaction is illegal and will generate a runtime error, rolling back the transaction and immediately terminating processing of the trigger and the batch. The only transaction control statements you should include in a trigger are ROLLBACK TRAN or SAVE TRAN .

Using Savepoints in Triggers

You can set a savepoint in a trigger and roll back to the savepoint. This will roll back only the operations within the trigger subsequent to the savepoint. The trigger and the transaction it is a part of will still be active until the transaction is subsequently committed or rolled back. The batch will continue processing.

Savepoints can be used to avoid a trigger arbitrarily rolling back an entire transaction. You can roll back to the named savepoint in the trigger and then issue a raiserror and return immediately to pass the error code back to the calling process. The calling process can then check the error status of the data modification statement and take appropriate action, either rolling back the transaction, rolling back to a savepoint in the transaction, or ignoring the error and committing the data modification.

The following is an example of a trigger using a savepoint:

 if object_id('tr_titles_i') is not null      drop trigger tr_titles_i go create trigger tr_titles_i on titles for insert as declare @rows int  -- create variable to hold @@rowcount select @rows = @@rowcount if @rows = 0 return save tran titlestrig if update(pub_id) and (select count(*)         from inserted i, publishers p         where p.pub_id = i.pub_id ) != @rows   begin        rollback transaction titlestrig        raiserror ('Invalid pub_id inserted', 16, 1)   end return 

This trigger will roll back all work since the savepoint and return with an error number of 50000. In the transaction, you can check for the error number and make the decision whether to continue the transaction, roll back the transaction, or if savepoints were set in the transaction, roll back to a savepoint and let the transaction continue. The following example rolls back the entire transaction if either of the first two inserts fail, but only rolls back the third if it fails, allowing the first two to be committed:

 begin tran add_titles  insert titles (title_id, pub_id, title)        values ('BU1234', '0736', 'Tuning SQL Server') if @@error = 50000 -- roll back entire transaction and abort batch    begin    rollback tran add_titles    return    end insert titles (title_id, pub_id, title)        values ('BU1236', '0877', 'Tuning SQL Server')  if @@error = 50000 -- roll back entire transaction and abort batch    begin    rollback tran add_titles    return    end save tran keep_first_two  -- set savepoint for partial rollback insert titles (title_id, pub_id, title)        values ('BU1235', 'abcd', 'Tuning SQL Server')  if @@error = 50000  -- roll back to save point, continue batch    begin    rollback tran keep_first_two    end commit tran 

TIP

Important reminder: When using a savepoint inside the trigger, the trigger is not rolling back the transaction. Therefore, the batch will not be automatically aborted. You must explicitly return from the batch after rolling back the transaction to prevent subsequent statements from executing.

NOTE

Don't forget to re-enable the constraint on the titles table when you are finished testing:

 alter table titles check constraint FK__titles__pub_id__014935CB