How to create and fire a SQL Server Trigger

When implemented correctly, SQL Server Triggers are a powerful mechanism for adding useful functionality to a database without the need to change any application code.

In this article, I look at what a SQL Server Trigger is, a template for defining a new Trigger and possible use cases.

A few facts

You may already be familiar with SQL Server Stored Procedures, however, Triggers differ somewhat (although Triggers are essentially a special type of Stored Procedure under the covers).

A couple of the key differences can be summarised as follows.

• Triggers do not accept any parameters.
• Extra information is made available to Triggers at execution time via virtual tables (more on this later).

Put simply, Triggers can be thought of as SQL scripts that are executed automatically whenever a specific event occurs e.g. whenever changes are made to data within a table.

Triggers are part of the database schema in the same way that constraints and indexes are attached to a table. As a result, you can count on the logic contained within a Trigger to be executed reliably without a dependency on any other moving parts. This is in contrast to the likes of SQL Server Agent Jobs which require the SQL Server Agent service to be running in order for them to execute successfully.

There are special ‘Inserted’ and ‘Deleted’ logical tables which can be accessed within the context of a Trigger and these give Triggers the ability to see what changes have been made and make decisions based on this additional information.

It is important to be aware that Triggers fire one time per command that makes a change to the table which the Trigger is attached to e.g. if you create a Trigger that fires on update commands and you execute a single SQL statement that updates 100 records within a particular table in one go, the Trigger associated with that table will only fire once.

Note that there are DML (Data Manipulation Language) Triggers that can be fired on INSERT, UPDATE, or DELETE commands and there are DDL (Data Definition Language) Triggers that can be fired on CREATE, ALTER, and DROP commands.

I am going to cover DML Triggers in the sections below.

Let’s move on and look at a basic Trigger template so that we can put things into context.

Trigger template

The basic outline of the SQL structure required to create a Trigger can be defined as follows.

CREATE TRIGGER TriggerName
ON dbo.TableName
FOR UPDATE
AS
BEGIN
    -- SQL statements.
END

In the first two lines, the Trigger is given a name and the table to attach the Trigger to is specified.

Note that Triggers can also be attached to views.

On the third line, the type of Trigger is defined. This can be any one of the following.

• FOR
• AFTER
• INSTEAD OF

Combined with one of the following.

• INSERT
• UPDATE
• DELETE

e.g. FOR UPDATE or AFTER INSERT (FOR and AFTER behave in the same manner).

Note that you can also combine the DML keywords with commas e.g. FOR INSERT, UPDATE so that a Trigger fires for both inserts and updates.

I have glossed over some of the extra options you can specify when creating a Trigger, but they aren’t that commonly used in my experience so I’ve left them out for simplicity. You can check out all of the available options in the Microsoft Docs.

A practical example

Now let’s look at a more in-depth example where a Trigger is used to track any changes that are made to the Stock Level of products in the AdventureWorks database.

Before proceeding, go ahead and download the AdventureWorks database (if you don’t have it already) and restore it into a suitable instance of SQL Server which you have appropriate access to.

Within the newly restored AdventureWorks database, add a new column to the standard dbo.DimProduct table using the SQL ALTER statement below.

ALTER TABLE dbo.DimProduct ADD StockLevel INT NOT NULL DEFAULT 0;

Note that I’ve specified the column type as an INT for the sake of simplicity in our example.

Now create a new table that will be used to store the audit trail of Stock Level changes. Execute the SQL CREATE script below.

CREATE TABLE [dbo].[DimProductStockLevelAudit](
    [ProductKey] [int] NOT NULL,
    [ProductAlternateKey] [nvarchar](25) NULL,
    [OldStockLevel] [int] NOT NULL,
    [NewStockLevel] [int] NOT NULL,
    [DateChanged] [datetime] NOT NULL
) ON [PRIMARY]

Note that the ProductAlternateKey column is essentially the unique Product Code or identifier for a product as defined in the dbo.DimProduct table.

Now for the Trigger…

CREATE TRIGGER StockLevelAudit
ON dbo.DimProduct
FOR UPDATE
AS
BEGIN
    -- Only process if the Stock Level has been updated.
    IF UPDATE(StockLevel)
    BEGIN
	-- Inserting using the SELECT ensures that we capture all Stock Level changes.
	-- Triggers fire one time per command that makes a change to the table.
	INSERT INTO dbo.DimProductStockLevelAudit 
	    (ProductKey, 
	     ProductAlternateKey, 
	     OldStockLevel, 
	     NewStockLevel, 
	     DateChanged) 
	SELECT 
	    i.ProductKey, 
	    i.ProductAlternateKey, 
	    d.StockLevel,
	    i.StockLevel,
	    GETDATE()
	FROM Inserted i -- 'Inserted' contains copies of the new values which have been updated in the virtual table.
	JOIN Deleted d ON i.ProductKey = d.ProductKey -- 'Deleted' contains copies of the old values which have been replaced.
	WHERE i.StockLevel <> d.StockLevel -- Only log changes if the Stock Level is different.
    END
END

The above Trigger follows the same pattern as the basic template which was documented in the previous section.

First, the Trigger is given a name i.e. ‘StockLevelAudit’, then the name of the table to attach the Trigger to is specified i.e. dbo.DimProduct.

The Trigger will fire whenever an UPDATE command has been executed against the table, as per the FOR UPDATE statement.

The main Trigger logic will only be processed if the ‘StockLevel’ column has been updated as a result of the IF UPDATE(StockLevel) conditional check.

Next, we insert records into the dbo.DimProductStockLevelAudit table by retrieving the column values we are interested in from the ‘Inserted’ and ‘Deleted’ logical tables.

The ‘Deleted’ table is joined to the ‘Inserted’ table on the ProductKey column.

Lastly, we ensure that we only create an audit table record if the Stock Level was actually changed, as opposed to just being updated to the same value.

Test firing

After creating the Trigger, try updating the ‘StockLevel’ column in the dbo.DimProduct table for a few products.

You should see a record in the dbo.DimProductStockLevelAudit table for every Stock Level value that was updated.

As you can imagine, Triggers that are similar to the one we have just defined have the potential to be very useful for many different auditing and logging requirements.

Other use cases could include updating a column based on the value of another column and correcting issues at the database level until a software fix can be applied.

Reloading…

Now that you’ve seen how a Trigger can be fired whenever particular columns in a table have been updated, I recommend that you try creating a Trigger that executes whenever INSERT or DELETE commands are issued.

INSTEAD OF Triggers can also be useful, and as the name suggests, they are executed instead of applying the results of the command which triggered them. This is a very powerful concept, but must be applied with caution since an INSTEAD OF Trigger completely overrides the original command which triggered it.

In conclusion, SQL Server Triggers are yet another useful tool in your DBA arsenal which are applicable to a number of different scenarios.

Lastly, in addition to the main script included in this article, I have a number of other SQL scripts which you may find useful in my SQL Scripts repository on GitHub.