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:
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.
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.
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:
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:
Removing a Trigger
You can remove a trigger from a table or view in the Object Explorer window:
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:
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.
Choosing the option above will create some system-generated code which you can then edit to change the way your trigger works.
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:
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.
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.
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:
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:
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...
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.
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.
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:
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.
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:
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:
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:
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:
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:
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.
 |  |