SQL 2005+

SQL Server Triggers

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:

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.

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)

Checking Which Columns Were Changed

It is often important to understand which columns were affected by a data change and to modify the behaviour of a trigger accordingly. This can be achieved using the Update function. This function accepts a single parameter, which holds the name of a column. It returns true if the column has been affected by the change that fired the trigger and false otherwise.

We can demonstrate the Update function by creating a new trigger. The sample below creates a trigger that runs when a user attempts to modify a customer row. If the BusinessName column has been changed, the transaction is rolled back and an error raised. If the business name has not been changed, the update is accepted.

CREATE TRIGGER DisallowBusinessNameChange
ON Customers
AFTER UPDATE
AS
IF UPDATE(BusinessName)
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('Business names may not be changed', 16, 1)
END

With the DisallowBusinessNameChange trigger created, the following update is allowed because the business name is not included.

UPDATE
    Customers
SET
    FirstName = 'Janet',
    LastName = 'Middleton'
WHERE
    CustomerNumber = 6

The UPDATE statement below is prevented from successfully executing because the BusinessName column is being set.

UPDATE
    Customers
SET
    BusinessName = 'Red Ltd'
WHERE
    CustomerNumber = 6

Accessing Inserted, Updated and Deleted Data

When a trigger is executed, two conceptual temporary tables are created in memory. These tables are named inserted and deleted and both have the same schema as the table being updated. When new rows are being added to a table, a copy of each row is present in the inserted table and the deleted table is empty. For deletions, the data that has been removed from the table is copied into the deleted table but the inserted table has no rows. For updates, both tables are populated with the inserted table containing the new data and deleted holding a copy of the old information. These tables allow you to see a "before and after" picture of the data within the trigger's scope.

It is possible to process the rows in either of the conceptual tables one by one using a cursor. However, this is discouraged as it can cause poor performance. Instead, it is advised that only rowset processing commands are used. These are generally limited to queries as the information in the inserted and deleted tables may not be modified.

To demonstrate the use of the inserted table we will add some new functionality to the JoBS database. Whenever a new job is created or updated, the engineer responsible for the job must be notified that a change has occurred. The fictitious DisasterFix company will send an appropriate message to the engineer's personal digital assistant (PDA). The means by which the message is sent is not important. For our purposes we will insert a row into a new table for every new or updated job. The row will contain the job's ID and the table will become a logical queue of messages to be processed by another software service. To create the new table, execute the following command:

CREATE TABLE PdaQueue
(
    JobId UNIQUEIDENTIFIER 
)

We can now create the trigger that populates the PdaQueue table. This trigger fires whenever an INSERT or UPDATE is detected. It inserts one row into the PdaQueue for each row found in the inserted table.

CREATE TRIGGER SendJobsToPDA
ON Jobs
AFTER INSERT, UPDATE
AS
INSERT INTO PdaQueue
(JobId)
SELECT JobId FROM inserted

To see the trigger in action, change the EngineerId for every job to engineer 9 as follows:

After the update, you should see one row in the PdaQueue table for each job affected.

Instead Of Triggers

The second type of trigger that we will consider is the INSTEAD OF trigger. This type of trigger fires before any data is changed and before any constraints are checked. INSTEAD OF triggers completely replace the action requested by the user with an alternative command or series of statements. Unlike AFTER triggers, they can be created for views as well as tables. Only one INSTEAD OF trigger is permitted per table or view.

The most common use of an INSTEAD OF trigger is to allow data to be inserted into a view that would not normally permit it. For example, the JoBS database includes a view named "PartList". This returns a list of parts defined in the Parts table but does not include the unit cost of the part. If you try to insert a row into the view, the operation fails because no value can be provided for the mandatory UnitCost column in the underlying table. The following INSTEAD OF trigger enables the creation of new rows in the view by intercepting insertions and inserting into the Parts table with a default unit cost instead.

CREATE TRIGGER PartListInsert
ON PartList
INSTEAD OF INSERT
AS
INSERT INTO Parts
    (PartNumber, PartName, UnitCost)
SELECT PartNumber, PartName, -1 FROM inserted

Try inserting a new row into and viewing the part with its default unit cost:

INSERT INTO PartList values ('10COELBO', '10mm Copper Pipe Elbow')
SELECT * FROM Parts WHERE PartNumber = '10COELBO'

Disabling a Trigger

You may wish to disable a trigger when performing maintenance tasks or bulk insertions of data. This can be because the trigger slows the operation or because the trigger's actions are unsuitable. A common example is when you are recreating archived data using a bulk insertion operation. To disable a trigger without deleting it use the DISABLE TRIGGER command. This requires the name of the trigger and the name of the table it is linked to.

After the operation, re-enable the trigger with a command similar to the following:

Dropping Triggers

If you no longer require the trigger and wish to delete it permanently, use the DROP TRIGGER command: