"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Whenever I am given the exciting task of looking into a new database, one of the first things that I look at is Data Manipulation Language (DML) defined triggers.  I am not sure quite why I do so, but learning about a new database is kind of like peeling an onion.  You’ve got to start somewhere.  Sometimes to my chagrin, the more layers I peel off, the more depressing it gets.  Today we’ll describe one example that can lead me to that.

Finding the triggers in a database is relatively simple using the following query, which also shows the parent table on which they are defined.

SELECT a.[object_id], b.name, b.[Type], b.type_desc, b.modify_date
    ,[Tigger On]=c.type_desc
    ,[Trigger Parent]=c.name
FROM sys.sql_modules a
JOIN sys.all_objects b ON a.[object_id] = b.[object_id]
JOIN sys.all_objects c ON b.[parent_object_id] = c.[object_id]
WHERE b.[Type] = 'TR'
ORDER BY b.[Type], b.name;

Note that this searches all databases in your SQL Server instance, so you may want to limit the results to just one specific database.

Sample Tables and Data to get us Started

In order to create a trigger you must first have a table.  In our case, we’ll borrow a couple of sample tables and data from a prior article kindly published earlier by SSC (How to Design, Build and Test a Dynamic Search Stored Procedure).

CREATE TABLE dbo.Shipments (
   Consignment_No     VARCHAR(30) NOT NULL
  ,ReferenceNo        VARCHAR(30) NULL
  ,CustID             VARCHAR(20) NULL
  ,Total_Pkgs         INT NOT NULL
  ,Last_Status        VARCHAR(12) NULL
  ,Last_Status_DT     DATETIME NULL
  ,PRIMARY KEY CLUSTERED ( Consignment_No )
 );
CREATE TABLE Shipment_History (
   Consignment_No      VARCHAR(30) NOT NULL FOREIGN KEY REFERENCES Shipments ( Consignment_No )
  ,Status_DT          DATETIME NOT NULL
  ,Status_Code        VARCHAR(12) NOT NULL
  ,Pkg_No             INT NOT NULL DEFAULT ( 0 )
  ,PRIMARY KEY CLUSTERED ( Consignment_No, Status_Code, Status_DT, Pkg_No )
 );
GO
-- Clean up your sandbox 
--DROP TABLE dbo.Shipment_History; 
--DROP TABLE dbo.Shipments;

Here is some sample data to populate the Shipments table, which you can add immediately if you are following along.

-- 8 consignments for two different customers. 
-- Some shipments have no customer yet assigned. 
INSERT INTO dbo.Shipments 
 ( Consignment_No, ReferenceNo, CustID, Total_Pkgs )
 VALUES  ( '8033701', 'PO 12345', 'ACME', 1 )
        ,( '0152037', NULL, 'ACME', 1 )
        ,( '4292860', NULL, 'ATLAS', 1 )
        ,( '0806473', 'INV 43523', 'ATLAS', 1 )
        ,( '6289811', NULL, 'APPLE', 1 )
        ,( '0642191', 'SO 1111', 'APPLE', 1 )
        ,( '8363496', NULL, NULL, 1 ) 
        ,( '7271931', NULL, NULL, 1 );
SELECT *
 FROM dbo.Shipments;

And some additional data to populate the Shipment_History table.  Before you run this however, let’s take a look at what columns are present in the Shipments table.

-- Some history records for these shipments
INSERT INTO dbo.Shipment_History 
  ( Consignment_No, Status_Code, Status_DT, Pkg_No )
 VALUES ( '8033701', 'SIP', '2013-10-19 22:19', 1 )
       ,( '8033701', 'WGT', '2013-10-20 18:47', 1 )
       ,( '8033701', 'MDE', '2013-10-21 10:47', 0 )
       ,( '8033701', 'OPS', '2013-10-21 21:43', 0 )
       ,( '8033701', 'POD', '2013-10-22 02:44', 0 )
       ,( '0152037', 'SIP', '2013-10-23 00:31', 1 )
       ,( '0152037', 'WGT', '2013-10-23 17:03', 1 )
       ,( '0152037', 'MDE', '2013-10-23 20:12', 0 )
       ,( '4292860', 'SIP', '2013-10-23 21:00', 1 )
       ,( '4292860', 'MDE', '2013-10-23 22:06', 0 )
       ,( '4292860', 'POD', '2013-10-24 10:54', 0 )
       ,( '0806473', 'SIP', '2013-10-25 04:16', 1 )
       ,( '0806473', 'MDE', '2013-10-25 08:49', 0 )
       ,( '6289811', 'SIP', '2013-10-25 14:28', 1 )
       ,( '6289811', 'WGT', '2013-10-26 05:35', 1 )
       ,( '6289811', 'OPS', '2013-10-26 20:08', 0 )
       ,( '6289811', 'POD', '2013-10-27 14:45', 0 )
       ,( '0642191', 'SIP', '2013-10-28 06:06', 1 )
       ,( '0642191', 'MDE', '2013-10-28 13:11', 0 )
       ,( '8363496', 'SIP', '2013-10-29 00:36', 1 )
       ,( '7271931', 'SIP', '2013-10-29 04:01', 1 )
       ,( '7271931', 'WGT', '2013-10-29 11:37', 1 );
SELECT *
 FROM dbo.Shipment_History;
SELECT *
 FROM dbo.Shipments;

If we look at the columns inserted into the Shipments table, two of the tables’ columns (Last_Status and Last_Status_DT) are not populated.  The reason is that this data is de-normalized from the information in the Shipment_History table.  I will not argue with the pundits that will say that you should not de-normalize data in this fashion, except to say that there are cases where it is appropriate to do so and one of those cases is for performance reasons.  The latter is the case here, were we to examine the real life scenario on which this sample data is based.

We have not shown the results of only creating the rows in the Shipments table, but the important thing to know is that those two columns that were not included in the insert will be NULL for all rows.

The Wrong Way to Write a TRIGGER

Oftentimes when I look into the TRIGGERs in a database, I’ll see one that looks something like this.

CREATE TRIGGER dbo.Shipment_History_INS ON dbo.Shipment_History AFTER INSERT AS BEGIN
DECLARE   @Consignment_No VARCHAR(30)
         ,@Status_Code       VARCHAR(12)
         ,@Last_Status_DT    DATETIME;
SELECT @Consignment_No=Consignment_No
      ,@Status_Code=Status_Code
      ,@Last_Status_DT=Status_DT
 FROM INSERTED;
UPDATE dbo.Shipments
  SET Last_Status     = @Status_Code
     ,Last_Status_DT = @Last_Status_DT
  WHERE Consignment_No = @Consignment_No;
END
GO

This is what I call a “single-use” trigger, but you also may hear it referred to as a “single-row” trigger  Ultimately, the correct terminology for it is a “totally wrongly-written” TRIGGER.  It is a blatantly RBAR-esque expression of T-SQL.  In case you’ve never heard the term “RBAR” before, this stands for “Row-By-Agonizing Row” and is a Moden-ism (a term coined by SQL MVP Jeff Moden).  Its meaning should be pretty clear, but essentially what it means is that SQL queries should be set-based and not be based on processing one row at a time.

This trigger has multiple problems:

• If status codes are not processed in the order that they are received, i.e., oldest to newest, the last status code isn’t necessarily what is put on the Shipments table record.
• It simply doesn’t work properly if more than one status code is inserted in one SQL query.

Microsoft SQL Server fires a trigger (in this case after an INSERT) once.  When you insert many records in one SQL query, the trigger still fires only once.  In that case, the INSERTED virtual table available in the trigger and referenced in the sample code above contains many rows.  The select into the local variables then will assign only one of those rows to data captured in the local variables, and since there is no ordering inherent in the INSERTED table’s results you can’t predict which row that will be.

Let’s now see what happens when we insert the data into our Shipment_History table, using the code that we suggested to defer running.  The result of the final select looks like this:

Consignment_No  ReferenceNo  CustID   Total_Pkgs  Last_Status  Last_Status_DT
0152037         NULL         ACME     1           NULL         NULL
0642191         SO 1111      APPLE    1           NULL         NULL
0806473         INV 43523    ATLAS    1           NULL         NULL
4292860         NULL         ATLAS    1           NULL         NULL
6289811         NULL         APPLE    1           NULL         NULL
7271931         NULL         NULL     1           NULL         NULL
8033701         PO 12345     ACME     1           SIP          2013-10-19 22:19:00.000
8363496         NULL         NULL     1           NULL         NULL

The key thing to observe in the results set is that only consignment 8033701 has non-NULL value for Last_Status and Last_Status_DT, because this “totally wrongly-written” trigger was only run once and only updated a single row in the Shipments table.

The wrong assumption used when a developer created this trigger is that any inserts to the Shipment_History table will comprise a single row.  While that may be true at the beginning of an application’s life, and no issues are initially detected with a trigger of this nature, you can certainly bet that at some point in time someone’s going to come along and decide that it is more efficient to insert many history records in one insert, and break this trigger!

Right now, experienced T-SQL developers will probably be thinking “oh come on, nobody writes triggers this way!”  My answer to that is that I see it all the time in sample code posted to the forums, so yes people still do write triggers that way, most probably due to lack of understanding or the RBAR-esque thinking I alluded to earlier.  This article is intended for those folks that have never really understood triggers in this way and need an example to guide them.

The Right Way to Write a Trigger

Here is a trigger that makes use of all the rows kindly provided to us in the INSERTED virtual table.

ALTER TRIGGER dbo.Shipment_History_INS ON dbo.Shipment_History
AFTER INSERT AS
BEGIN
    WITH LastStatus AS
    (
        SELECT Consignment_No, Status_Code, Status_DT
        FROM
        (
            SELECT Consignment_No, Status_Code, Status_DT
                ,rn=ROW_NUMBER() OVER (PARTITION BY Consignment_No ORDER BY Status_DT DESC)
            FROM INSERTED
        ) a
        WHERE rn=1
    )
    UPDATE a
    SET Last_Status = Status_Code
        ,Last_Status_DT = Status_DT
    FROM dbo.Shipments a
    JOIN LastStatus b ON a.Consignment_No = b.Consignment_No;
END
GO

This trigger also ensures that the latest effective date present in the INSERTED table’s rows, is the status code and date/time that is applied to the Shipments table row.  This is identified by using the ROW_NUMBER() (where it =1) and sorting by Status_DT descending.

In order to further our understanding, below are the results returned by just the LastStatus CTE:

Consignment_No  Status_Code  Status_DT
0152037         MDE          2013-10-23 20:12
0642191         MDE          2013-10-28 13:11
0806473         MDE          2013-10-25 08:49
4292860         POD          2013-10-24 10:54
6289811         POD          2013-10-27 14:45
7271931         WGT          2013-10-29 11:37
8033701         POD          2013-10-22 02:44
8363496         SIP          2013-10-29 00:36

You may be wondering how exactly I knew that.  Well, you can easily simulate the INSERTED virtual table using a CTE to debug code in your TRIGGER, like the following query which takes the VALUES clause from the INSERT statement provided earlier.

WITH INSERTED AS
(
    SELECT Consignment_No, Status_Code, Status_DT, Pkg_No
    FROM
    (
        VALUES ( '8033701', 'SIP', '2013-10-19 22:19', 1 )   
            ,( '8033701', 'WGT', '2013-10-20 18:47', 1 )  
            ,( '8033701', 'MDE', '2013-10-21 10:47', 0 )   
            ,( '8033701', 'OPS', '2013-10-21 21:43', 0 )    
            ,( '8033701', 'POD', '2013-10-22 02:44', 0 )   
            ,( '0152037', 'SIP', '2013-10-23 00:31', 1 )   
            ,( '0152037', 'WGT', '2013-10-23 17:03', 1 )   
            ,( '0152037', 'MDE', '2013-10-23 20:12', 0 )   
            ,( '4292860', 'SIP', '2013-10-23 21:00', 1 )   
            ,( '4292860', 'MDE', '2013-10-23 22:06', 0 )   
            ,( '4292860', 'POD', '2013-10-24 10:54', 0 )   
            ,( '0806473', 'SIP', '2013-10-25 04:16', 1 )    
            ,( '0806473', 'MDE', '2013-10-25 08:49', 0 )   
            ,( '6289811', 'SIP', '2013-10-25 14:28', 1 )  
            ,( '6289811', 'WGT', '2013-10-26 05:35', 1 )   
            ,( '6289811', 'OPS', '2013-10-26 20:08', 0 )   
            ,( '6289811', 'POD', '2013-10-27 14:45', 0 )   
            ,( '0642191', 'SIP', '2013-10-28 06:06', 1 )  
            ,( '0642191', 'MDE', '2013-10-28 13:11', 0 )  
            ,( '8363496', 'SIP', '2013-10-29 00:36', 1 )   
            ,( '7271931', 'SIP', '2013-10-29 04:01', 1 )  
            ,( '7271931', 'WGT', '2013-10-29 11:37', 1 )
    ) a (Consignment_No, Status_Code, Status_DT, Pkg_No )
),
    LastStatus AS
(
    SELECT Consignment_No, Status_Code, Status_DT
    FROM
    (
        SELECT Consignment_No, Status_Code, Status_DT
            ,rn=ROW_NUMBER() OVER (PARTITION BY Consignment_No ORDER BY Status_DT DESC)
        FROM INSERTED
    ) a
    WHERE rn=1
)
SELECT *
FROM LastStatus;

That’s a trick you’ll probably want to remember.  Now after truncating the Shipment_History table and rerunning the INSERT statement, the final results in the Shipments table looks like this:

Consignment_No  ReferenceNo  CustID  Total_Pkgs  Last_Status  Last_Status_DT
0152037         NULL         ACME    1           MDE          2013-10-23 20:12:00.000
0642191         SO 1111      APPLE   1           MDE          2013-10-28 13:11:00.000
0806473         INV 43523    ATLAS   1           MDE          2013-10-25 08:49:00.000
4292860         NULL         ATLAS   1           POD          2013-10-24 10:54:00.000
6289811         NULL         APPLE   1           POD          2013-10-27 14:45:00.000
7271931         NULL         NULL    1           WGT          2013-10-29 11:37:00.000
8033701         PO 12345     ACME    1           POD          2013-10-22 02:44:00.000
8363496         NULL         NULL    1           SIP          2013-10-29 00:36:00.000

Trigger Types and the Associated Virtual Tables

There are two types of triggers in T-SQL: AFTER and INSTEAD OF.

• AFTER triggers arguably the more common of the two) fire after the work of the query is done.
• INSTEAD OF triggers, as the type implies, fire instead of the work being done by the query.

DML triggers in T-SQL can fire on any or all of the following events:

• INSERT
• UPDATE
• DELETE

An individual trigger may fire on one, two or all three of those events.  A MERGE statement may fire a trigger more than once, depending on which of the three directives is present in its syntax (recall that you can use INSERT, UPDATE and DELETE directives in a MERGE), or it may fire various triggers if there is a different one for each event.

The two virtual tables available to you while any DML trigger is firing are:

• INSERTED – shown in the example above.
• DELETED – includes rows being deleted.

While both virtual tables are available in any trigger, sometimes they are empty (devoid of rows):

• INSERTED – no rows present on DELETE events.
• DELETED – no rows present on INSERT events.

On an UPDATE event, there will be an equivalent number of rows in each virtual table, with the DELETED table containing the old values (before the UPDATE) and the INSERTED table containing the new value (after the UPDATE).  This may sound a little counterintuitive, but trust me when I say it can be extremely handy!

Some Other Helpful Hints and Notes

Here are some other helpful hints and notations on the use of DML triggers:

• When using the SELECT/INTO query context, T-SQL fires no triggers for the obvious reason that this creates the target table so at the time the statement executes there cannot be a trigger to fire.  Another way of looking at this is that triggers don’t fire on SELECT statements, and effectively SELECT/INTO is a special case of a SELECT statement.
• A trigger should always be minimalistic in the sense that it should only do the work it needs to do and no more.  Overloading triggers with all kinds of stuff that can be done elsewhere can cause serious performance problems.
• When you CREATE or ALTER a trigger, just like any other stored procedure, SQL Server performs deferred names resolution.  So if you reference a table that doesn’t exist at the time you CREATE/ALTER, you will not know about that until the trigger is actually fired.  In other words, the CREATE/ALTER will not throw an error.
• While there may be more than one After trigger specified for a specific event, the order that the TRIGGERs fire can only be partially controlled.  You can specify which fires first and which fires last using sp_settriggerorder.
• Triggers fire only after the underlying SQL executes successfully, including after all constraint checks have been verified.  Note that constraints checks are not done prior to running INSTEAD OF triggers.
• Normally if you execute a standalone (“atomic”) INSERT, UPDATE, DELETE or MERGE statement you don’t really need to put it into a tranaction (you can).  However if any of those statements causes a trigger to fire, it can no longer be considered atomic and you should put it into a transaction, and use the proper error handling and rollback mechanism in the event that an error occurs during the action of the trigger.

This really only scratches the surface of all the rules for using triggers, so I suggest you consult Microsoft Books-on-Line if you’d like to know more.  It should be enough to get you started though.

Conclusions

Triggers are a remarkable tool that can make de-normalizing data when it is appropriate to do so very simple.  They can also do many other things, and they can be overused and improperly used, but our hope in this spackle article is that you’ll now understand the right and the wrong ways to write them.

Dwain Camps

SQL Enthusiast

Follow me on Twitter: @DwainCSQL

© Copyright Dwain Camps Mar 5, 2015 All Rights Reserved