SQL 2005+

SQL Server Triggers (2)

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.

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