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.

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:

UPDATE Jobs SET EngineerId = 9

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

SELECT * FROM PdaQueue

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.

DISABLE TRIGGER PartListInsert ON PartList

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

ENABLE TRIGGER PartListInsert ON PartList

Dropping Triggers

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

DROP TRIGGER PartListInsert