Rolling Back Trigger Transactions

You will recall that earlier in this chapter I advised that SQL Server treats the code in a trigger as a transaction. This means that you can undo the statements that are enclosed in the trigger transaction code. It is important to understand that when you issue a ROLLBACK TRANSACTION, as demonstrated in the following code, the entire batch in the trigger is reversed out. Also, you do not necessarily need to call ROLLBACK TRANSACTION if you are relying only on SQL Server to trap an error. SQL Server automatically reverses any transaction it determines to be fatal.

 CREATE TRIGGER VoiceMessageDelete ON Messages FOR DELETE  AS IF EXISTS    (SELECT delete FROM Messages m INNER JOIN conference c       ON m.message_no = c, message_no)  BEGIN      RAISERROR ('Message cannot be deleted until heard by       all conference members', 10, 1)       ROLLBACK TRANSACTION END