SQL Server Triggers

TimChapman — Mon, 01 Mar 2010 01:44:00 GMT

Handling trigger is a very simple, and very misunderstood facet of SQL Server programming. Today I'll take a look some very common trigger scenarios, some mistakes made, and how you can program around them.

First, a little bit of background of the types of triggers available in SQL Server.

Trigger Types
There are two different types of SQL Server triggers to date: INSTEAD OF triggers and AFTER triggers.

INSTEAD OF triggers are placed on tables and views (most often views) and are invoked INSTEAD OF the action taken. For example, if I have defined an INSTEAD OF trigger on a view to execute in the event of an INSERT statement, the trigger would be invoked INSTEAD OF my INSERT statement and actions could be taken to handle the intended statement. This is ideal for views because it allows the developer to abstract triggers on the view code so that the view behaves like a table and without the overhead of the user knowing how to appropriately handle with the underlying tables. For example, I could define a view named vw_AllSalesView that abstracts sales data among 10 different tables. I could then define an INSTEAD OF trigger on this view which will insert data into the appropriate table(s) when an INSERT statement is ran against the view.

AFTER triggers are actions that are taken AFTER an action is taken on a table. This is really the most common type of trigger, and is ran against tables (not views). For example, you have a situation where you need want to capture any new records inserted into a table into a processing table for later work. You could define an AFTER trigger on the primary table to capture the INSERTED records AFTER the action is taken.

It is very important to note the similarities and differences between an INSTEAD OF and an AFTER trigger. Both trigger types execute in the context of the original transaction. The INSTEAD OF trigger overtakes the transaction and completes its work rather than the original DML statement. The scope of a transaction of an AFTER trigger ends when the original statement has completed AND after the work by the AFTER trigger. The major differences between the two trigger types is the scope of work that they accomplish. Remember that the INSTEAD OF replaces in full the work of the original intended DML statement. The AFTER trigger still allows the original statement to occur, and runs after it has completed.

Pseudo Tables
So, how does a trigger have access to the records that were inserted/modified/or deleted (or intended to be inserted/modified/or deleted in the case of an INSTEAD OF trigger)? Well, the records from the modifications are stored in two different internal SQL Server tables.

INSERTED
The INSERTED pseudo-table stores records that were newly inserted into a table in the case of an INSERT statement. This table also contains the new values for the records that were modified through the use of an UPDATE statement.

DELETED
The DELETED pseudo-table stores records that were deleted from the table through a DELETE statement. This table also contains the old values for the records that were modified through the use of an UPDATE statement.

Granularity
All SQL Server triggers are handled at a set-based granularity. This means that whatever records are updated by a certain statement will also be available in the INSERTED and DELETED tables. This is one of the primary problems I have seen when triggers are written. SQL Server does not have a "FOR EACH ROW" feature when handling trigger records. In the event (usually a RARE event) that you need to take special actions on each record in one of the pseudo tables, such as calling a procedure for each value, a loop construct is how you'll need to accomplish it. (More on this later.)

So, lets take a look at a few common trigger-based programming mistakes and what you can do to get around them. (Think SET-BASED) First, lets create a table and load some data into it.

IF OBJECT_ID('SalesHistory') IS NOT NULL
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(     
    [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,     
    [Product] [varchar](50) NULL,     
    [SaleDate] [datetime] NULL,     
    [SalePrice] [money] NULL
)
GO    

DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=100)
BEGIN                 
    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)                     
    VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))          

    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)     
    VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                      

    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)            
    VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                      

    SET @i = @i + 1    

END

Now to the trigger. Below is the format that I sometimes see on forum posts, usually along with the complaint of "Why isn't my trigger working?" Lets take a look at why it doesn't really do what is intended.

CREATE TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN
    DECLARE @Product VARCHAR(50), @SaleID INT
    SET @Product = (SELECT Product FROM INSERTED)
    SET @SaleID = (SELECT SaleID FROM INSERTED)
    SELECT @Product, @SaleID
END

Now I can run an UPDATE statement to see how well my fresh new trigger works.

UPDATE TOP(5) SalesHistory
SET SaleDate = SaleDate

My UPDATE statement causes an error. It seems that I have a sub-query that returns more than one value that I'm trying to assign to a variable. I am actually VERY lucky to receive this error. This tells me that something is very wrong with my code, and that I probably need to take a step back and reconsider how I'm handling it.

Msg 512, Level 16, State 1, Procedure tr_SalesHistory_Update, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

However, instead of rethinking how I'm writing my trigger, I'll do a little rearranging to resolve my error. This time I'll include the functionality of using the values from my INSERTED table to update an archive table. I'll also show some rows at the end of the trigger so I can verify what I am doing is what is intended.

ALTER TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN
    DECLARE @Product VARCHAR(50), @SaleID INT

    SELECT @Product = Product, @SaleID = SaleID
    FROM INSERTED

    UPDATE sf
    SET Product = @Product
    FROM SalesHistoryArchive sf
    WHERE SaleID = @SaleID

    SELECT @Product, Product, @SaleID, SaleID
    FROM INSERTED

END

Now, lets run the same UPDATE statement again:

UPDATE TOP(5) SalesHistory
SET SaleDate = SaleDate

Below is the output from my trigger. The first and the third columns represent the values from my variables that I captured from the INSERTED table and the second and fourth columns represent the actual values from the table. As you can see, my variables only contain the values from the first (just happened to be the first record in this case) record from my batch. I updated the SalesHistoryArchive table with these values, rather than from all of the values from the INSERTED table.

Luckily, the change for this is easy to make. I can use a set-based approach and get around the use of variable entirely. I can join my INSERTED table to the SalesHistoryArchive table to achieve my desired result.

ALTER TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN

    UPDATE sf
    SET Product = i.Product
    FROM SalesHistoryArchive sf
    JOIN INSERTED i ON sf.SaleID = i.SaleID

END

Handling Each Value
So, what can you do if you need to call a stored procedure for each value altered and captured in your trigger? There are two main ways to handle this situation; store the records in a table and handle them later, or iterate through them in the trigger and call the procedure. The first way is relatively straightforward (and by far the best option of the two). In this scenario, a queue-table of sorts is used to capture output from the INSERTED or DELETED tables in the trigger. Later, a process acts on the records in this table and cleans them up once its work is done. Iteration is still likely used in this scenario, but not at the expense of the original transaction as would be the case if a WHILE LOOP or CURSOR were used in a trigger.

With the cursor approach, the INSERTED or DELETED records are loaded into a cursor (or WHILE LOOP) and the data set is iterated. At each iteration, the values from one row in the pseduo table is passed to a stored procedure. The main drawback to this apporach is that the entire data set must be iterated through BEFORE the intial transaction has completed. If it was a large update, the scope of the transaction becomes much larger, causing the duration of the locks on the table to increase. This causes nasty blocking and potentially deadlocks. When faced with such a situation, I strongly encourage you to store the captured values in a table for later processing. It requires a little extra work up front, but pays off in the end due to less stress on the database.

Tim

Tip of the Week : SQL Server, INSERTED

SQL Server Triggers