Triggers – SQL Server


To totally unlock this section you need to Log-in


Login

In SQL Server, a trigger is a special type of stored procedure which will run automatically in response to some kind of event in the database.

Explanation

Triggers are commonly used to perform auditing actions, to maintain table integrity in place of native constraints such as foreign keys and check constraints, and to perform other post DML processing. Triggers operate under the scope of a transaction so if a table were updated, for instance, the update would occur and the trigger would fire. While the trigger is at work, the transaction would not be committed until the trigger completed (or rolled back in the case of failure). If a lot of processing is being done in the trigger, locks will be held until the trigger completes. This is an important point to note: Triggers extend the life of a transaction.

Also, due to their stealthiness, they can make troubleshooting data issues difficult and tedious.

The use of triggers to accomplish common integrity checking is probably not a good idea since they do extend transaction life. Moreover, if there is an integrity violation, a ROLLBACK on any modified data will have to occur which can potentially cause a performance bottleneck as the application waits for the rollback to complete. In contrast, native constraints do their checks prior to any modification and, as a result, do not cause a ROLLBACK to occur if a violation occurs.

When triggers fire, there are virtual tables that hold the values of the data before and after the modification. These tables are called inserted and deleted. When accessing these virtual tables within trigger code, you should work on their data as a set. One common mistake we see over and over and over in trigger code: a trigger is written with the assumption it will always work on a single row at a time. This is not the case.

Types of Trigger

There are three main types of trigger in SQL Server, as described in the table below:

  • DML Trigger: DML, or Data Manipulation Language triggers respond to events which manipulate the data in your database. Typically, this type of trigger would be attached to events such as INSERT, UPDATE or DELETE statements which change the data in a table or a view.
  • DDL Trigger:A DDL, or Data Definition Language trigger would be executed when objects within a database are modified. CREATE, ALTER and DROP are good examples of statements which could execute a DDL trigger.
  • Logon Trigger: Logon triggers are fired when a user session is being established.

Remind what is DML?

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back.

Why use DML Triggers?

In many respects DML triggers are similar to constraints and you can use them in a similar way to enforce business rules and data integrity. The main benefits of using a DML trigger rather than a constraint are shown in the table below:

Triggers in SQL Server

Types of DML Trigger

There are two types of DML trigger: AFTER and INSTEAD OF. The names of these triggers are fairly indicative of when they run! An AFTER trigger will be executed immediately after the event which triggered it has run successfully. An INSTEAD OF trigger replaces the event which originally called the trigger.

Creating a DML Trigger in the Object Explorer

You can add a DML trigger to a table or a view in the Object Explorer window. To do this, expand the table or view and then right-click the Triggers folder.

Triggers in SQL Server

When you select the option shown above SQL Server will create a new query window full of system-generated code which you can then modify to create your trigger.

Triggers in SQL Server

As with many examples of system-generated code it is often easier to write your own code from scratch.

Writing Code to Create a DML Trigger

You can write code to create a trigger in any query window, as long as the statement which creates the trigger is the first one in a batch. The basic syntax for an AFTER trigger is shown in the example below:

CREATE TRIGGER trgMyAfterTrigger

ON tblMyTable
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
--the instructions for the trigger go here
END

The AFTER statement controls which data modification events your trigger will handle. You can choose to handle all three events with the same trigger, as we've done here; create a trigger which handles any two events; or write a separate trigger for each event. You can also have more than one AFTER trigger for each event.

The basic syntax for an INSTEAD OF trigger is very similar to that for an AFTER trigger:

CREATE TRIGGER trgMyInsteadOfTrigger

ON tblMyTable
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
--the instructions for the trigger go here
END

Again, you can write your trigger to handle all three data modification events or any single event or pair of events. The main difference is that you can only have one INSTEAD OF trigger for each event.

An Example of a Simple AFTER Trigger

The example below prints a message whenever an actor is added to, deleted from or modified in the tblActor table:

CREATE TRIGGER trgActorsChanged

ON tblActor
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
PRINT 'Something happened to tblActor'
END

After executing the code to create this trigger, any modifications to the data in the table of actors will cause a message to be printed. The example below inserts a new record, modifies it and, finally, deletes it:

--Turn off row counts

SET NOCOUNT ON
--Add a new record into tblActor
INSERT INTO tblActor(ActorID,ActorName)
VALUES (999,'Test actor')
--Modify the record that was added
UPDATE tblActor
SET ActorDOB = GETDATE()
WHERE ActorID = 999
--Delete the record
DELETE FROM tblActor
WHERE ActorID = 999

When the code above is executed we see the following output in the Messages window:

Triggers in SQL Server

An Example of a Simple INSTEAD OF Trigger

When you create an INSTEAD OF trigger, the instructions you provide are executed in place of the event which causes the trigger to be fired. The code shown below creates a trigger which generates an error message whenever someone attempts to insert a new record into the table of actors:

CREATE TRIGGER trgActorsInserted

ON tblActor
INSTEAD OF INSERT
AS
BEGIN
RAISERROR('No more actors can be added',16,1)
END

Once the trigger has been created, any attempt to insert a new record into the table of actors will fail and the error message will be displayed:

Triggers in SQL Server

Removing a Trigger

You can remove a trigger from a table or view in the Object Explorer window:

Triggers in SQL Server

Click OK on the dialog box which appears to delete the trigger.

You can also delete a trigger in code, as shown below:

DROP TRIGGER trgActorsChanged

Disabling and Enabling a Trigger

Rather than deleting a trigger you may just want to disable it and re-enable it later. You can do this in the Object Explorer, as shown below:

Triggers in SQL Server

You can also disable and enable a trigger using code, as shown in the example below:

--Disable a DML trigger

DISABLE TRIGGER trgActorsInserted ON tblActor
GO
--Enable a DML trigger
ENABLE TRIGGER trgActorsInserted ON tblActor
GO

Modifying a Trigger

You can change the way a trigger works either by using the Object Explorer or by writing some code.

Triggers in SQL Server

Choosing the option above will create some system-generated code which you can then edit to change the way your trigger works.

Triggers in SQL Server

You could, of course, just have written the code out from scratch.

Specifying the First and Last DML Trigger

If you have created multiple AFTER triggers on the same table you can set which one will run first and which will run last. To do this you can use a system stored procedure called sp_settriggerorder, as shown below:

--Set this trigger to be the first one executed

EXEC sp_settriggerorder
@triggername = 'trgActorsInserted'
,@order = 'first'
,@stmttype = 'INSERT'
--Set this trigger to be the last one executed
EXEC sp_settriggerorder
@triggername = 'trgActorsInserted'
,@order = 'last'
,@stmttype = 'INSERT'

The stored procedure has three parameters which are explained in the table below:

Triggers in SQL Server

If you want to remove a specific order from a trigger you can use the same stored procedure, setting the @order parameter to 'none'.

The Inserted and Deleted Tables

Often it's useful to have access to the data that has been modified when you're creating a trigger and, for this reason, SQL Server provides you with the inserted and deleted tables. These tables are generated automatically in memory whenever data is modified in a table or view and you can access their rows in your trigger code.

The table below describes how these tables are used in different DML trigger events.

Triggers in SQL Server

The simple trigger shown below will select any records that have been added into the actor table whenever an insert event occurs:

CREATE TRIGGER trgActorsInserted

ON tblActor
AFTER INSERT
AS
BEGIN
SELECT * FROM inserted
END

When the trigger has been created, any time a record is added to the table of actors its details will be shown in the Results pane.

Triggers in SQL Server

Using the Inserted Table for Validation

One advantage of using a trigger instead of a constraint is that a trigger can refer to columns in other tables. In this example we'll create an AFTER trigger which fires whenever a new record is inserted into the table of cast members. The trigger will check the actor table to ensure that the actor we are trying to assign to the role is still alive (it's a morbid example I admit, but hopefully it demonstrates how you could use triggers to test business logic).

CREATE TRIGGER trgNewCastMember

ON tblCast
AFTER INSERT
AS
BEGIN
IF EXISTS
(
SELECT *
FROM
tblActor AS a
JOIN inserted AS i
ON a.ActorID=i.CastActorID
WHERE
a.ActorDateOfDeath IS NOT NULL
)
BEGIN
RAISERROR('That actor is no longer alive',16,1)
ROLLBACK TRANSACTION
RETURN
END
END

Whenever a record is inserted into the cast table the trigger checks to see if there is a record in the actor table with the corresponding ActorID whose ActorDateOfDeath field is not null. If there is a record which matches these criteria an error message is raised and the transaction is rolled back. The result of attempting to insert a deceased actor into the cast table is shown in the diagram below:

Triggers in SQL Server

DDL Triggers

DDL, or Data Definition Language triggers are executed when objects in your database are changed in some way. The main SQL keywords to which a DDL trigger can be attached are CREATE, ALTER and DROP.

There are several other keywords that can fire a DDL trigger, including GRANT, DENY, REVOKE and UPDATE STATISTICS. It's also worth noting that some system stored procedures can cause a DDL trigger to fire.

When DDL Triggers Run

DDL triggers always run after the event to which they are attached. There's no equivalent of the INSTEAD OF type of DML trigger. There is a long list of DDL events at the Microsoft Developer Network site.

If you want to find out which events have caused your DDL triggers to fire you can use the EVENTDATA function.

DDL Trigger Scope

DDL triggers can be scoped to either a single database, or to every database on a server. You can find DDL triggers in the relevant folders in the Object Explorer window, as shown in the diagrams below:

Triggers - SQL Server

Creating a DDL Trigger in the Object Explorer

You can create a database-scoped DDL trigger from the Object Explorer window. To do this, right-click the Database Triggers folder within the Programmability folder of the database and choose New Database Trigger...

Triggers - SQL Server

Once you've chosen the option shown above you'll be presented with a page of system-generated code which you'll then have to adapt to create your trigger.

Triggers - SQL Server

Creating a DDL Trigger in Code

Rather than adapting the system-generated code shown above, you could write the code from scratch. The example shown below creates a simple DDL trigger, scoped to a single database:

USE Movies
GO
CREATE TRIGGER trgNoNewTables
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT 'No more tables, please'
ROLLBACK
END

Triggers that are scoped to the database appear in the Database Triggers folder within the database.

Triggers - SQL Server

The trigger is designed to prevent the creation of new tables in the database. If somebody attempts to execute code to create a new table they will see an error message, as shown in the diagram below:

Triggers - SQL Server

Creating a Server-Scoped Trigger

To create a trigger which affects all of the databases on a server you can exchange the word DATABASE with the words ALL SERVER in the code which creates your trigger:

CREATE TRIGGER trgNoNewTables
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
PRINT 'No more tables, please'
ROLLBACK
END

You'll find this type of trigger in the Triggers folder of the server.

Triggers - SQL Server

Modifying a DDL Trigger

You can change the way a trigger works by writing an ALTER TRIGGER statement. The example shown below extends our example trigger to respond to the ALTER_TABLE and DROP_TABLE events:

ALTER TRIGGER trgNoNewTables
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'You cannot create, alter or drop tables'
ROLLBACK
END

Removing a DDL Trigger

You can remove a DDL trigger using a couple of different methods. You can use the Object Explorer, as shown below:

Triggers - SQL Server

You can also write code to delete a trigger; the example code below shows how to do this for both a database-scoped and server-scoped trigger:

--Drop a database-scoped trigger
DROP TRIGGER trgNoNewTables ON DATABASE
GO
--Drop a server-scoped trigger
DROP TRIGGER trgNoNewTables ON ALL SERVER
GO

Disabling and Enabling a DDL Trigger

You can disable and enable a DDL trigger using either the Object Explorer or by writing code. To use the Object Explorer to disable and enable a DDL trigger:

Triggers - SQL Server

You can also use code to enable and disable DDL triggers:

--Disable a database-scoped trigger
DISABLE TRIGGER trgNoNewTables ON DATABASE
GO
--Enable a database-scoped trigger
ENABLE TRIGGER trgNoNewTables ON DATABASE
GO
--Disable a server-scoped trigger
DISABLE TRIGGER trgNoNewTables ON ALL SERVER
GO
--Enable a server-scoped trigger
ENABLE TRIGGER trgNoNewTables ON ALL SERVER
GO

You can also choose to disable all of the triggers within a certain scope, as shown in the examples below:

--Disable all database-scoped triggers
DISABLE TRIGGER ALL ON DATABASE
GO
--Enable all database-scoped triggers
ENABLE TRIGGER ALL ON DATABASE
GO
--Disable all server-scoped triggers
DISABLE TRIGGER ALL ON ALL SERVER
GO
--Enable all server-scoped triggers
ENABLE TRIGGER ALL ON ALL SERVER
GO

Setting the Order of DDL Triggers

Just as with DML triggers you can set the order of DDL triggers using the sp_settriggerorder system stored procedure. The example below shows how to do this:

--Set this trigger to be the first one executed
EXEC sp_settriggerorder
@triggername = 'trgNoNewTables'
,@order = 'first'
,@stmttype = 'CREATE_TABLE'
,@namespace = 'DATABASE'

The first three parameters are the same as for DML triggers. The fourth parameter, @namespace, refers to the scope of the trigger and can be either 'DATABASE' or 'SERVER'.

NOTE: You can have both a server-scoped and database-scoped trigger handling the same DDL event. Server-scoped triggers will always fire before any of the database-scoped ones.

Logon Triggers

While DML and DDL triggers work based on changes to the data or objects in a database, logon triggers detect when users log on to the server and execute stored procedures in response to this.

Typical uses for logon triggers include keeping track of user activity, restricting access to SQL Server and maintaining a limit to the number of concurrent users.

Basic Syntax of Logon Triggers

The basic syntax for a logon trigger is shown in the example below:

CREATE TRIGGER myLogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
--Your trigger code here
END

You can find logon triggers in the same place as server-scoped DDL triggers, as shown in the diagram below:

Triggers - SQL Server

NOTE: You can drop, alter, disable and enable logon triggers using the same methods as for other types of trigger. You can also change the order in which logon triggers fire using the sp_settriggerorder system stored procedure.

When do Logon Triggers Fire?

The answer to this question seems obvious: when a user logs on! It's worth considering exactly what this means in terms of what you can do within a logon trigger. According to Microsoft's TechNet site a logon trigger fires when the authentication stage of logging on has finished but before the user session is actually established.

This means that if your trigger is designed to show messages to the user (using the PRINT statement for example) the user won't ever see them.

Getting Information about User Sessions

To make effective use of logon triggers it helps to be able to find information on the sessions that are currently established. The example code below shows how to do this:

--Return data on the current sessions
SELECT * FROM sys.dm_exec_sessions

An example of the output from this query is shown in the diagram below:

Triggers - SQL Server

Two of the more useful columns from this output are login_name and is_user_process.

An Example to Prevent Users Establishing More Sessions

The example shown below prevents a certain user from establishing any more connections to the server when they already have three connections open.

CREATE TRIGGER trgNoMoreConnections
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'WiseOwl' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'WiseOwl') > 3
ROLLBACK
END

The ORIGINAL_LOGIN function shown in the code above returns the name of the user who is logging in. In the WHERE clause of the query we're testing if the is_user_process column contains a value of 1 (i.e. it is a user process), and if the original_login_name column is equal to the user whose sessions we're trying to limit.

The result of attempting to open a new session when the user already has three open is an error message similar to the one shown below:

Triggers - SQL Server

NOTE: Take care when creating logon triggers as you can fairly easily find yourself unable to connect to a SQL Server instance! If this happens you'll need somebody who is part of the sysadmin role to connect and disable or delete the trigger.