SQL 2005+

SQL Server Triggers

by Richard Carr, published at http://www.blackwasp.co.uk/SQLTriggers.aspx

The sixtieth and final part of the SQL Server Programming Fundamentals tutorial considers the use of triggers. SQL Server triggers can be defined against tables and views to intercept insert, update and delete statements and modify their results.

What are Triggers?

Triggers are a special type of stored procedure. Instead of being executed manually, they run automatically in response to events that occur in a database. In this article we will examine triggers that run when data is inserted, updated or deleted. There are other types of trigger supported by SQL Server 2005 that allow actions to be performed when the schema of a database is changed or when a user logs on. These are beyond the scope of this tutorial.

Triggers are often used to audit changes to data by recording when modifications are made and, optionally, the previous version of the information. They are also used to enforce business rules and data integrity when those rules are too complex to be controlled by primary keys, foreign keys, unique constraints or check constraints. In such cases, rules may be checked and, if broken, data changes can be disallowed, transactions can be rolled back and errors may be raised.

Triggers can be overused and some developers prefer not to use triggers at all. The key problem is that triggers execute automatically and their effect is not always immediately obvious. This can lead to unexpected results when the existence of a trigger is not known. However, triggers can be very powerful tools when used correctly. As always, one should consider all alternatives and choose the technology appropriate to the requirements.

Creating Triggers

In this article we will create and demonstrate triggers using the JoBS database. This is the database that has been created during the course of the tutorial. You can download the script to generate the database using the link at the top of this page.

Creating a Simple Trigger

The first type of trigger that we will consider is the AFTER trigger. Such triggers execute after data is inserted, updated or deleted from a table. The Transact-SQL (T-SQL) syntax for creating an AFTER trigger is as follows:

CREATE TRIGGER trigger-name
ON table-name
AFTER events
AS
statement-1
statement-2
.
.
statement-x

The first changeable element is trigger-name. This allows you to provide a unique name for the trigger. The table-name element specifies which table the trigger is linked to. Following the AFTER clause is a comma-separated list of actions that cause the trigger to run. At least one of INSERT, UPDATE and DELETE must be included in the list but any combination of the three actions is permitted. If you wish to have a different reaction for each event type, you can define more than one AFTER trigger per table. Finally, the body of the trigger is defined as a series of T-SQL statements. These do not need to be contained between BEGIN and END keywords.

To demonstrate a simple trigger, run the following script. This creates a trigger named "DisallowSkillCreation" that fires when an attempt is made to insert data into the Skills table. To prevent any user from inserting a new skill without permission from an administrator, the trigger immediately rolls back the current transaction and raises an error. The error is generated using the RAISERROR command, a keyword we have not previously seen in the tutorial.

CREATE TRIGGER DisallowSkillCreation
ON Skills
AFTER INSERT
AS
ROLLBACK TRANSACTION
RAISERROR('Only administrators may create skills', 16, 1)

With the trigger created, it is impossible to break the new business rule by inserting a new row. Try executing the following:

INSERT INTO Skills VALUES ('NEW', 'New Skill')

If the trigger has been created correctly, you should see an error similar to that shown below and the new row will not have been created.

Msg 50000, Level 16, State 1, Procedure DisallowSkillCreation, Line 6
Only administrators may create skills
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

To create a similar trigger that runs when a user tries to modify or delete one or more rows in the Skills table, run the sample script below:

CREATE TRIGGER DisallowSkillChanges
ON Skills
AFTER UPDATE, DELETE
AS
ROLLBACK TRANSACTION
RAISERROR('Only administrators may modify or delete skills', 16, 1)

If you try to update a row you should see an error similar to that for inserting but with a different error message. However, if you try to delete all of the rows from the table you will see a different message.

DELETE FROM Skills

In this case, the error is related to a foreign key constraint. As the trigger fires after any data is modified, the constraints prevent the action before the trigger code is executed.

Modifying a Trigger

Triggers can be modified using the MODIFY TRIGGER command. The syntax is similar to that of CREATE TRIGGER. Run the following script to change the message displayed when attempting to create a new skill.

ALTER TRIGGER DisallowSkillCreation
ON Skills
AFTER INSERT
AS
ROLLBACK TRANSACTION
RAISERROR('Only administrators may create new skills', 16, 1)