"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".
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:
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.
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.
Follow me on Twitter: @DwainCSQL
© Copyright Dwain Camps Mar 5, 2015 All Rights Reserved