SQL Server provides triggers as a means of executing T-SQL code in response to database object, database, and server events. SQL Server 2012 implements three types of triggers: classic T-SQL Data Manipulation Language (DML) triggers, which fire in response to INSERT, UPDATE, and DELETE events against tables; Data Definition Language (DDL) triggers, which fire in response to CREATE, ALTER, and DROP statements; and logon triggers, which fire in response to LOGON events. DDL triggers can also fire in response to some system SPs that perform DDL-like operations.

Triggers are a form of specialized SP, closely tied to your data and database objects. In the past, DML triggers were used to enforce various aspects of business logic, such as foreign key and other constraints on data, and other more complex business logic. Cascading declarative referential integrity (DRI) and robust check constraints in T-SQL have supplanted DML triggers in many areas, but they are still useful in their own right. In this chapter, we will discuss how triggers work, how to use them, and when they are most appropriate. We will also discuss DDL triggers and explore their use.

DML Triggers

DML triggers are composed of T-SQL code that is executed (fired) in response to an INSERT, an UPDATE, a DELETE, or a MERGE statement on a table or view. DML triggers are created via the CREATE TRIGGER statement, which allows you to specify the following details about the trigger:

• The name of the trigger, which is the identifier you can use to manage the trigger. You can specify a two-part name for a trigger (schema and trigger name), but the schema must be the same as the schema for the table on which the trigger executes.
• The table or view on which the trigger executes.
• The triggering events, which can be any combination of INSERT, UPDATE, and DELETE. The triggering events indicate the type of events that the trigger fires in response to.
• The AFTER/FOR or INSTEAD OF indicators, which determine whether the trigger is fired after the triggering statement completes or the trigger overrides the firing statement.
• Additional options like the ENCRYPTION and EXECUTE AS clauses, which allow you to obfuscate the trigger source code and specify the context that the trigger executes under, respectively.

In addition to the CREATE TRIGGER statement, SQL Server provides an ALTER TRIGGER statement to modify the definition of a trigger, a DROP TRIGGER statement to remove an existing trigger from the database, and DISABLE TRIGGER and ENABLE TRIGGER statements to disable and enable a trigger, respectively.

Listing 6-1 shows how to disable and enable a specific trigger named HumanResources.EmployeeUpdateTrigger, or all triggers on an object, namely, the HumanResources.Employee table. It also contains an example of how to query the sys.triggers catalog view to return all the disabled triggers in the current database.

Listing 6-1.  Disabling and Enabling Triggers

DISABLE TRIGGER HumanResources.EmployeeUpdateTrigger
ON HumanResources.Employee;
  
SELECT
    name,
    OBJECT_SCHEMA_NAME(parent_id) +  '.'  + OBJECT_NAME(parent_id) as Parent
FROM sys.triggers
WHERE is_disabled = 1;
  
ENABLE TRIGGER HumanResources.EmployeeUpdateTrigger
ON HumanResources.Employee;
  
-- disabling and enabling all triggers on the object
DISABLE TRIGGER ALL ON HumanResources.Employee;
ENABLE TRIGGER ALL ON HumanResources.Employee;

Disabling triggers can greatly improve performance when you apply a batch of modifications on a table. Just make sure, of course, that the rules enforced by the trigger(s) will be checked in another way, for instance manually after the batch. Do not forget also to re-enable the trigger at the end of the process.

EXEC sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE';

When to Use DML Triggers

Way back in the day, using triggers was the best (and in some cases only) way to perform a variety of tasks, such as ensuring cascading DRI, validating data before storing it in tables, auditing changes, and enforcing complex business logic. Newer releases of SQL Server have added functionality that more closely integrates many of these functions into the core database engine. For instance, in most cases, you can use SQL Server’s built-in cascading DRI to ensure referential integrity and check constraints for simple validations during insert and update operations. DML triggers are still a good choice when simple auditing tasks or validations with complex business logic are required.

Listing 6-2 shows a very simple trigger that we created on the HumanResources.Employee table of the AdventureWorks database. The HumanResources.EmployeeUpdateTrigger trigger simply updates the ModifiedDate column of the HumanResources.Employee table with the current date and time whenever a row is updated.

Listing 6-2.  HumanResources.EmployeeUpdateTrigger Code

CREATE TRIGGER HumanResources.EmployeeUpdateTrigger
ON HumanResources.Employee
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
    -- stop if no row was affected
    IF @@ROWCOUNT = 0 RETURN
    -- Turn off "rows affected" messages
    SET NOCOUNT ON;
  
    -- Make sure at least one row was affected
    -- Update ModifiedDate for all affected rows
    UPDATE HumanResources.Employee
    SET ModifiedDate = GETDATE()
    WHERE EXISTS
     (
        SELECT 1
        FROM inserted i
        WHERE i.BusinessEntityID = HumanResources.Employee.BusinessEntityID
    );
END;

The first part of the CREATE TRIGGER statement defines the name of the trigger and specifies that it will be created on the HumanResources.Employee table. The definition also specifies that the trigger will fire after rows are updated, and the NOT FOR REPLICATION keywords prevent replication events from firing the trigger.

CREATE TRIGGER HumanResources.EmployeeUpdateTrigger
ON HumanResources.Employee
AFTER UPDATE
NOT FOR REPLICATION

The body of the trigger starts by checking the number of rows affected by the UPDATE with the @@ROWCOUNT system function. This is an optimization that skips the body of the trigger if no rows were affected.

Whenever any trigger is fired, it is implicitly wrapped in the same transaction as the DML statement that fired it. This has big performance and concurrency implications. What it means is that whatever your trigger does, it should do it as quickly and efficiently as possible. The T-SQL statements in your trigger body can potentially create locks in your database, a situation that you want to minimize. It is not unheard of for inefficient triggers to cause blocking problems. You should also minimize the amount of work done inside the trigger and optimize the operations it has to perform.  It also means that a ROLLBACK TRANSACTION statement in the trigger will roll back DML statements executed in the trigger, as well as the original DML statement that fired the trigger (and all explicit transactions in which the statement is run, for that matter).

Checking @@ROWCOUNT at the start of your trigger helps ensure that your triggers are efficient. If @@ROWCOUNT is 0, it means that no rows were affected by the original DML statement that fired the trigger. Then your trigger has no work to do, and you can skip the rest.

-- stop if no row was affected
IF @@ROWCOUNT = 0 RETURN

Next, the trigger turns off the rows affected messages via the SET NOCOUNT ON statement.

-- Turn off "rows affected" messages
SET NOCOUNT ON;

The IF statement contains an UPDATE statement that sets the ModifiedDate column to the current date and time when rows in the table are updated. An important concept of trigger programming is to be sure that you account for multiple row updates. It’s not safe to assume that a DML statement will update only a single row of your table, because triggers in SQL Server are set-oriented and fire only once for a statement. There is no such thing as a per-row trigger in SQL Server. In this trigger, the UPDATE statement uses the EXISTS predicate in the WHERE clause to ensure that ModifiedDate is updated for every row that was affected. It accomplished this by using the inserted virtual table, described in the “The inserted and deleted Virtual Tables” sidebar in this section.

-- Update ModifiedDate for all affected rows
UPDATE HumanResources.Employee
SET ModifiedDate = GETDATE()
WHERE EXISTS
(
    SELECT 1
    FROM inserted i
    WHERE i.BusinessEntitylD = HumanResources.Employee.BusinessEntitylD
);

Testing the trigger is as simple as using SELECT and UPDATE. The sample in Listing 6-3 changes the marital status of employees with BusinessEntityID numbers 1 and 2 to M (for “married”).

Listing 6-3.  Testing HumanResources.EmployeeUpdateTrigger

UPDATE HumanResources.Employee
SET MaritalStatus = 'M'
WHERE BusinessEntityID IN (1, 2);
  
SELECT BusinessEntityID, NationalIDNumber, MaritalStatus, ModifiedDate
FROM HumanResources.Employee
WHERE BusinessEntityID IN (1, 2);

The results, shown in Figure 6-1 demonstrate that the UPDATE statement fired the trigger and properly updated the ModifiedDate for the two specified rows.

Auditing with DML Triggers

Another common use for DML triggers is auditing DML actions against tables. The primary purpose of DML auditing is to maintain a record of changes to the data in your database. This might be required for a number of reasons, including regulatory compliance or to fulfill contractual obligations.

The first step to implementing DML auditing is to create a table to store your audit information. Listing 6-4 creates just such a table.

Listing 6-4.  DML Audit Logging Table

CREATE TABLE dbo.DmlActionLog (
    EntryNum int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    SchemaName sysname NOT NULL,
    TableName sysname NOT NULL,
    ActionType nvarchar(10) NOT NULL,
    ActionXml xml NOT NULL,
    LoginName sysname NOT NULL,
    ApplicationName sysname NOT NULL,
    HostName sysname NOT NULL,
    ActionDateTime datetime2(0) NOT NULL DEFAULT (SYSDATETIME())
);
GO

The dbo.DmlActionLog table in Listing 6-4 will store information for each DML action performed against a table, including the name of the schema and table against which the DML action was performed, the type of DML action performed, XML-formatted snapshots of the before and after states of the rows affected, and additional information to identify who performed the DML action and when the action was performed. Once the audit logging table is created, it’s time to create a trigger to log DML actions. This is shown in Listing 6-5.

Listing 6-5.  DML Audit Logging Trigger

CREATE TRIGGER HumanResources.DepartmentChangeAudit
ON HumanResources.Department
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
    -- stop if no row was affected
    IF @@ROWCOUNT = 0 RETURN
  
    -- Turn off "rows affected" messages
    SET NOCOUNT ON;
    DECLARE @ActionType nvarchar(10), @ActionXml xml;
  
    -- Get count of inserted rows
    DECLARE @inserted_count int = (
        SELECT COUNT(*)
        FROM inserted
    );
    -- Get count of deleted rows
    DECLARE @deleted_count int = (
        SELECT COUNT(*)
        FROM deleted
    );
  
    -- Determine the type of DML action that fired the trigger
    SET @ActionType = CASE
        WHEN (@inserted_count > 0) AND (@deleted_count = 0) THEN N'insert'
        WHEN (@inserted_count = 0) AND (@deleted_count > 0) THEN N'delete'
        ELSE N'update'
    END;
  
    -- Use FOR XML AUTO to retrieve before and after snapshots of the changed
    --  data  in  XML  format
    SELECT @ActionXml = COALESCE
    (
        (
            SELECT  *
            FROM  deleted
            FOR  XML  AUTO
        ),  N'  <  deleted/>'
    )  +  COALESCE
    (
        (
            SELECT  *
            FROM  inserted
            FOR  XML  AUTO
        ),  N'  <  inserted/>'
    );
  
    -- Insert a row for the logged action in the audit logging table
    INSERT INTO dbo.DmlActionLog
    (
        SchemaName,
        TableName,
        ActionType,
        ActionXml,
        LoginName,
        ApplicationName,
        HostName
    )
    SELECT
        OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
        OBJECT_NAME(t.parent_id, DB_ID()),
        @ActionType,
        @ActionXml,
        SUSER_SNAME(),
        APP_NAME(),
        HOST_NAME()
    FROM sys.triggers t
    WHERE t.object_id = @@PROCID;
END;
GO

The trigger in Listing 6-5 is created on the HumanResources.Department table, although it is written in such a way that the body of the trigger contains no code specific to the table it’s created on. This means you can easily modify the trigger to work as-is on most tables.

The HumanResources.DepartmentChangeAudit trigger definition begins with the CREATE TRIGGER statement, which names the trigger and creates it on the HumanResources.Department table. It also specifies that the trigger should fire after INSERT, UPDATE, or DELETE statements are performed against the table. Finally, the NOT FOR REPLICATION clause specifies that replication events will not cause the trigger to fire.

CREATE TRIGGER HumanResources.DepartmentChangeAudit
ON HumanResources.Department
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION

The trigger body begins by checking the number of rows affected by the DML statement with the @@ROWCOUNT function. The trigger skips the remainder of the statements in the body if no rows were affected.

-- stop if no row was affected
IF @@ROWCOUNT = 0 RETURN

The main body of the trigger begins with an initialization that turns off extraneous rows affected messages, declares local variables, and gets the count of rows inserted and deleted by the DML statement from the inserted and deleted virtual tables.

-- Turn off "rows affected" messages
SET NOCOUNT ON;
  
DECLARE @ActionType nvarchar(10), @ActionXml xml;
  
-- Get count of inserted rows
DECLARE @inserted_count int = (
    SELECT COUNT(*)
    FROM inserted
);
-- Get count of deleted rows
DECLARE @deleted_count int = (
    SELECT COUNT(*)
    FROM deleted
);

Since the trigger is logging the type of DML action that caused it to fire (an INSERT, a DELETE, or an UPDATE action), it must determine the type programmatically. This can be done by applying the following simple rules to the counts of rows from the inserted and deleted virtual tables:

1. If at least one row was inserted but no rows were deleted, the DML action was an insert.
2. If at least one row was deleted but no rows were inserted, the DML action was a delete.
3. If at least one row was deleted and at least one row was inserted, the DML action was an update.

These rules are applied in the form of a CASE expression, as shown in the following:

    -- Determine the type of DML action that fired the trigger
    SET @ActionType = CASE
        WHEN (@inserted_count > 0) AND (@deleted_count = 0) THEN N'insert'
        WHEN (@inserted_count = 0) AND (@deleted_count > 0) THEN N'delete'
        ELSE N'update'
    END;

The next step in the trigger uses the SELECT statement’sFOR XML AUTO clause to generate XML-formatted before and after snapshots of the affected rows. FOR XML AUTO is useful because it automatically uses the source table name as the XML element name—in this case inserted or deleted. The FOR XML AUTO clause automatically uses the names of the columns in the table as XML attributes for each element. Because the inserted and deleted virtual tables have the same column names as this affected table, you don’t have to hard-code column names into the trigger. In the resulting XML, the < deleted > elements represent the before snapshot and the < inserted > elements represent the after snapshot of the affected rows.

-- Use FOR XML AUTO to retrieve before and after snapshots of the changed
--  data  in  XML  format
SELECT @ActionXml = COALESCE
(
    (
        SELECT  *
        FROM  deleted
        FOR  XML  AUTO
    ),  N'  <  deleted/>'
)  +  COALESCE
(
    (
        SELECT  *
        FROM  inserted
        FOR  XML  AUTO
    ),  N'  <  inserted/>'
);

The final step in the trigger inserts a row representing the logged action into the dbo.DmlActionLog table. Several SQL Server metadata functions—like @@PROCID, OBJECT_SCHEMA_NAME(), and OBJECT_NAME(), as well as the sys.triggers catalog view—are used in the INSERT statement to dynamically identify the current trigger procedure ID, and the schema and table name information. Also, functions like SUSER_SNAME(), APP_NAME(), and HOST_NAME() allow you to retrieve useful audit information on the execution context. Again, this means that almost nothing needs to be hard-coded into the trigger, making it easier to use the trigger on multiple tables with minimal changes.

-- Insert a row for the logged action in the audit logging table
INSERT INTO dbo.DmlActionLog
(
    SchemaName,
    TableName,
    ActionType,
    ActionXml,
    LoginName,
    ApplicationName,
    HostName
)
SELECT
    OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
    OBJECT_NAME(t.parent_id, DB_ID()),
    @ActionType,
    @ActionXml,
    SUSER_SNAME(),
    APP_NAME(),
    HOST_NAME()
FROM sys.triggers t
WHERE t.object_id = @@PROCID;

You can easily verify the trigger with a few simple DML statements. Listing 6-6 changes the name of the AdventureWorks Information Services department to Information Technology, and then inserts and deletes a Customer Service department. The results are shown in Figure 6-2.

Listing 6-6.  Testing the DML Audit Logging Trigger

UPDATE HumanResources.Department SET Name = N'Information Technology'
WHERE DepartmentId = 11;
INSERT INTO HumanResources.Department
(
    Name,
    GroupName
)
VALUES
(
    N'Customer Service',
    N'Sales and Marketing'
);
  
DELETE
FROM HumanResources.Department
WHERE Name = N'Customer Service';
  
SELECT
    EntryNum,
    SchemaName,
    TableName,
    ActionType,
    ActionXml,
    LoginName,
    ApplicationName,
    HostName,
    ActionDateTime

FROM dbo.DmlActionLog;

The FOR XML AUTO-generated ActionXml column data deserves a closer look. As we mentioned earlier in this section, the FOR XML AUTO clause automatically generates element and attribute names based on the source table and source column names. The UPDATE statement in Listing 6-6 generates the ActionXml entry shown in Figure 6-3. Note that we’ve formatted the XML for easier reading, but we have not changed the content.

Nested and Recursive Triggers

SQL Server supports triggers firing other triggers through the concept of nested triggers. A nested trigger is simply a trigger that is fired by the action of another trigger, on the same or a different table. Triggers can be nested up to 32 levels deep. We would advise against nesting triggers deeply, however, since the additional levels of nesting will affect performance. If you do have triggers nested deeply, you might want to reconsider your trigger design. Nested triggers are turned on by default, but you can turn them off with the sp_configure statement, as shown in Listing 6-7.

Listing 6-7.  Turning Off Nested Triggers

EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;
GO

Set the nested triggers option to 1 to turn nested triggers back on. This option affects only AFTER triggers. INSTEAD OF triggers can be nested and will execute regardless of the setting. Triggers can also be called recursively. There are two types of trigger recursion:

• Direct recursion: Occurs when a trigger performs an action that causes it to recursively fire itself.
• Indirect recursion: Occurs when a trigger fires another trigger (which can fire another trigger, etc.), which eventually fires the first trigger.

Direct and indirect recursion of triggers applies only to triggers of the same type. As an example, an INSTEAD OF trigger that causes another INSTEAD OF trigger to fire is direct recursion. Even if a different type of trigger is fired between the first and second firing of the same trigger, it is still considered direct recursion. For example, if one or more AFTER triggers are fired between the first and second firings of the same INSTEAD OF trigger, it is still considered direct recursion. Indirect recursion occurs when a trigger of the same type is called between firings of the same trigger.

You can use the ALTER DATABASE statement’s SET RECURSIVE_TRIGGERS option to turn direct recursion of AFTER triggers on and off, as shown in Listing 6-8. Turning off direct recursion of INSTEAD OF triggers requires that you also set the nested triggers option to 0, as shown previously in Listing 6-7.

Listing 6-8.  Turning Off Recursive AFTER Triggers

ALTER DATABASE AdventureWorks SET RECURSIVE_TRIGGERS OFF;

Actions taken with an INSTEAD OF trigger will not cause it to fire again. Instead, the INSTEAD OF trigger will perform constraint checks and fire any AFTER triggers. As an example, if an INSTEAD OF UPDATE trigger on a table is fired, and during the course of its execution performs an UPDATE statement against the table, the UPDATE will not fire the INSTEAD OF trigger again. Instead the UPDATE statement will initiate constraint check operations and fire AFTER triggers on the table.

The UPDATE() and COLUMNS_UPDATED() Functions

Triggers can take advantage of two system functions, UPDATE() and COLUMNS_UPDATED(), to tell you which columns are affected by the INSERT or UPDATE statement that fires the trigger in the first place. UPDATE() takes the name of a column as a parameter and returns true if the column is updated or inserted, and false otherwise. COLUMNS_UPDATED() returns a bit pattern indicating which columns are affected by the INSERT or UPDATE statement.

In case of an UPDATE, affected means that the column is present in the statement, not that the value of the column effectively changed. There is only one way to know if the value of a column really changed: by comparing the content of the deleted and inserted virtual tables. You can adapt the query example below to do that with your trigger.

SELECT i.ProductId, d.Color as OldColor, i.Color as NewColor
FROM deleted as d
JOIN inserted as i ON d.ProductId = i.ProductId
    AND COALESCE(d.Color, '') <> COALESCE(i.Color, ''),

This fragment is designed to be part of a trigger that could be created on the Production.Product table. The JOIN condition associates lines from the deleted and inserted tables on the primary key column and adds a non-equi join condition (joining on difference rather than on equivalence) on the Color column, to keep only rows where the Color value was changed. The COALESCE() function allows us to take into account the possibility of a NULL being present in the previous or new value.

Getting back to the UPDATE() and COLUMNS_UPDATED() functions, the sample trigger in Listing 6-9 demonstrates the use of triggers to enforce business rules. In this example, the trigger uses the UPDATE function to determine if the Size or SizeUnitMeasureCode has been affected by an INSERT or UPDATE statement. If either of these columns is affected by an INSERT or UPDATE statement, the trigger checks to see if a recognized SizeUnitMeasureCode was used. If so, the trigger converts the Size to centimeters. The trigger recognizes several SizeUnitMeasureCode values, including centimeters (CM), millimeters (MM), and inches (IN).

Listing 6-9.  Trigger to Enforce Standard Sizes

CREATE TRIGGER Production.ProductEnforceStandardSizes
ON Production.Product
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
    -- Make sure at least one row was affected and either the Size or
    -- SizeUnitMeasureCode column was changed
    IF (@@ROWCOUNT > 0) AND (UPDATE(SizeUnitMeasureCode) OR UPDATE(Size))
    BEGIN
        -- Eliminate "rows affected" messages
        SET NOCOUNT ON;
        -- Only accept recognized units of measure or NULL
        IF EXISTS
        (
            SELECT 1
            FROM inserted
            WHERE NOT
                ( SizeUnitMeasureCode IN (N'M', N'DM', N'CM', N'MM', N'IN')
                    OR SizeUnitMeasureCode IS NULL
                )
            )
        BEGIN
            -- If the unit of measure wasn't recognized raise an error and roll back
            -- the transaction
            RAISERROR ('Invalid Size Unit Measure Code.', 10, 127);
            ROLLBACK TRANSACTION;
        END
        ELSE
        BEGIN
            -- If the unit of measure is a recognized unit of measure then set the
            -- SizeUnitMeasureCode to centimeters and perform the Size conversion
            UPDATE Production.Product
            SET SizeUnitMeasureCode = CASE
                    WHEN Production.Product.SizeUnitMeasureCode IS NULL THEN NULL ELSE N'CM'                     END,
                Size = CAST (
                    CAST ( CAST(i.Size AS float) *
                        CASE i.SizeUnitMeasureCode
                            WHEN N'M' THEN 100.0
                            WHEN N'DM' THEN 10.0
                            WHEN N'CM' THEN 1.0
                            WHEN N'MM' THEN 0.10
                            WHEN N'IN' THEN 2.54
                        END
                    AS int
                    ) AS nvarchar(5)
                )
            FROM inserted i
            WHERE Production.Product.ProductID = i.ProductID
            AND i.SizeUnitMeasureCode IS NOT NULL;
        END;
    END;
END;
GO

The first part of the trigger definition gives the trigger its name, Production.ProductEnforceStandardSizes, and creates it on the Production.Product table. It is specified as an AFTER INSERT, UPDATE trigger and is declared as NOT FOR REPLICATION.

CREATE TRIGGER Production.ProductEnforceStandardSizes
ON Production.Product
AFTER INSERT, UPDATE
NOT FOR REPLICATION

The code in the body of the trigger immediately checks @@ROWCOUNT to make sure that at least one row was affected by the DML statement that fired the trigger, and uses the UPDATE function to ensure that the Size or SizeUnitMeasureCode columns were affected by the DML statement:

IF (@@ROWCOUNT > 0)
AND (UPDATE(SizeUnitMeasureCode) OR UPDATE(Size)) BEGIN
• • •
END;

Once the trigger has verified that at least one row was affected and the appropriate columns were modified, the trigger sets NOCOUNT ON to prevent the rows affected messages from being generated by the trigger. The IF EXISTS statement checks to make sure that valid unit-of-measure codes are used. If not, the trigger raises an error and rolls back the transaction.

-- Eliminate "rows affected" messages
SET NOCOUNT ON;
-- Only accept recognized units of measure or NULL
IF EXISTS
(
    SELECT 1
    FROM inserted
    WHERE NOT
        ( SizeUnitMeasureCode IN (N'M', N'DM', N'CM', N'MM', N'IN')
            OR SizeUnitMeasureCode IS NULL
        )
    )
BEGIN
    -- If the unit of measure wasn't recognized raise an error and roll back
    -- the transaction
    RAISERROR ('Invalid Size Unit Measure Code.', 10, 127);
    ROLLBACK TRANSACTION;
END

If the unit-of-measure validation is passed, the SizeUnitMeasureCode is set to centimeters and the Size is converted to centimeters for each inserted or updated row.

BEGIN
    -- If the unit of measure is a recognized unit of measure then set the
    -- SizeUnitMeasureCode to centimeters and perform the Size conversion
    UPDATE Production.Product
        SET SizeUnitMeasureCode = CASE
            WHEN Production.Product.SizeUnitMeasureCode IS NULL THEN NULL ELSE N'CM' END,
            Size = CAST (
                CAST ( CAST(i.Size AS float) *
                    CASE i.SizeUnitMeasureCode
                        WHEN N'M' THEN 100.0
                        WHEN N'DM' THEN 10.0
                        WHEN N'CM' THEN 1.0
                        WHEN N'MM' THEN 0.10
                        WHEN N'IN' THEN 2.54
                    END
                AS int
            ) AS nvarchar(5)
        )
    FROM inserted i
    WHERE Production.Product.ProductID = i.ProductID
    AND i.SizeUnitMeasureCode IS NOT NULL;
END;

This trigger enforces simple business logic by ensuring that standard-size codes are used when updating the Production.Product table and converting the Size values to centimeters. To test the trigger, you can perform updates of existing rows in the Production.Product table. Listing 6-10 updates the sizes of the products with ProductID 680 and 780 to 600 millimeters and 22.85 inches, respectively. The results, with the Size values automatically converted to centimeters, are shown in Figure 6-4.

Listing 6-10.  Testing the Trigger by Adding a New Product

UPDATE  Production.Product
SET Size = N'600',
    SizeUnitMeasureCode = N'MM'
WHERE  ProductId  =  680;
  
UPDATE  Production.Product
SET Size = N'22.85',
    SizeUnitMeasureCode = N'IN'
WHERE  ProductId  =  706;
  
SELECT  ProductID,
    Name,
    ProductNumber,
    Size,
    SizeUnitMeasureCode
FROM  Production.Product

WHERE  ProductID  IN  (680,  706);

While the UPDATE() function accepts a column name and returns true if the column is affected, the COLUMNS_UPDATED() function accepts no parameters and returns a varbinary value with a single bit representing each column. You can use the bitwise AND operator (&) and a bit mask to test which columns are affected. The bits are set from left to right, based on the ColumnID number of the columns from the sys.columns catalog view or the COLUMNPROPERTY() function.

To create a bit mask, you must use 2⁰ (1) to represent the first column, 2¹ (2) to represent the second column, and so on. Because COLUMNS_UPDATED() returns a varbinary result, the column indicator bits can be spread out over several bytes. To test columns beyond the first eight, like the Size and SizeUnitMeasureCode columns in the example code (columns 11 and 12), you can use the SUBSTRING function to return the second byte of COLUMNS_UPDATED() and test the appropriate bits with a bit mask of 12 (12 = 2² + 2³). The sample trigger in Listing 6-9 can be modified to use the COLUMNS_UPDATED() function, as shown here:

IF (@@ROWCOUNT > 0) AND (SUBSTRING(COLUMNS_UPDATED(), 2, 1) & 12 <> 0x00)

The COLUMNS_UPDATED() function will not return correct results if the ColumnID values of the table are changed. If the table is dropped and recreated with columns in a different order, you will need to change the triggers that use COLUMNS_UPDATED() to reflect the changes. There may be specialized instances in which you’ll be able to take advantage of the COLUMNS_UPDATED() functionality, but in general we would advise against using COLUMNS_UPDATED(), and instead use the UPDATE() function to determine which columns were affected by the DML statement that fired your trigger.

Triggers on Views

Although you cannot create AFTER triggers on views, SQL Server does allow you to create INSTEAD OF triggers on your views. A trigger can be useful for updating views that are otherwise nonupdatable, such as views with multiple base tables or views that contain aggregate functions. INSTEAD OF triggers on views also give you fine-grained control, since you can control which columns of the view are updatable through the trigger. The AdventureWorks database comes with a view named Sales.vSalesPerson, which is formed by joining 11 separate tables together. The INSTEAD OF trigger in Listing 6-11 allows you to update specific columns of two of the base tables used in the view by executing UPDATE statements directly against the view.

Listing 6-11.  INSTEAD OF Trigger on a View

CREATE TRIGGER Sales.vIndividualCustomerUpdate
ON Sales.vIndividualCustomer
INSTEAD OF UPDATE
NOT FOR REPLICATION
AS
BEGIN
    -- First make sure at least one row was affected
    IF @@ROWCOUNT = 0 RETURN
    -- Turn off "rows affected" messages
    SET NOCOUNT ON;
    -- Initialize a flag to indicate update success
    DECLARE @UpdateSuccessful bit = 0;
  
    -- Check for updatable columns in the first table
    IF UPDATE(FirstName) OR UPDATE(MiddleName) OR UPDATE(LastName)
    BEGIN
        -- Update columns in the base table
        UPDATE Person.Person
        SET FirstName = i.FirstName,
            MiddleName = i.MiddleName,
            LastName = i.LastName
        FROM inserted i
        WHERE i.BusinessEntityID = Person.Person.BusinessEntityID;
  
        -- Set flag to indicate success
        SET @UpdateSuccessful = 1;
    END;
    -- If updatable columns from the second table were specified, update those
    -- columns in the base table
    IF UPDATE(EmailAddress)
    BEGIN
        -- Update columns in the base table
        UPDATE Person.EmailAddress
        SET EmailAddress = i.EmailAddress
        FROM inserted i
        WHERE i.BusinessEntityID = Person.EmailAddress.BusinessEntityID;
  
        -- Set flag to indicate success
        SET @UpdateSuccessful = 1;
    END;
    -- If the update was not successful, raise an error and roll back the
    -- transaction
    IF @UpdateSuccessful = 0
        RAISERROR('Must specify updatable columns.', 10, 127);
END;
GO

The trigger in Listing 6-11 is created as an INSTEAD OF UPDATE trigger on the Sales.vIndividualCustomer view, as shown following:

CREATE TRIGGER Sales.vIndividualCustomerUpdate
ON Sales.vIndividualCustomer
INSTEAD OF UPDATE
NOT FOR REPLICATION

As with the previous examples in this chapter, this trigger begins by checking @@ROWCOUNT to ensure that at least one row was updated:

-- First make sure at least one row was affected
IF @@ROWCOUNT = 0 RETURN;

Once the trigger verifies that one or more rows were affected by the DML statement that fired the trigger, it turns off the rows affected messages and initializes a flag to indicate success or failure of the update operation:

-- Turn off "rows affected" messages
SET NOCOUNT ON;
-- Initialize a flag to indicate update success
DECLARE @UpdateSuccessful bit = 0;

The trigger then checks to see if the columns designated as updatable were affected by the UPDATE statement. If the proper columns were affected by the UPDATE statement, the trigger performs updates on the appropriate base tables for the view. For purposes of this demonstration, the columns that are updatable by the trigger are the FirstName, MiddleName, and LastName columns from the Person.Person table, and the EmailAddress column from the Person.EmailAddress column.

-- Check for updatable columns in the first table
IF UPDATE(FirstName) OR UPDATE(MiddleName) OR UPDATE(LastName)
BEGIN
    -- Update columns in the base table
    UPDATE Person.Person
    SET FirstName = i.FirstName,
        MiddleName = i.MiddleName,
        LastName = i.LastName
    FROM inserted i
    WHERE i.BusinessEntityID = Person.Person.BusinessEntityID;
  
    -- Set flag to indicate success
    SET @UpdateSuccessful = 1;
END;
  
-- If updatable columns from the second table were specified, update those
-- columns in the base table
IF UPDATE(EmailAddress) BEGIN
    -- Update columns in the base table
    UPDATE Person.EmailAddress
    SET EmailAddress = i.EmailAddress
    FROM inserted i
    WHERE i.BusinessEntityID = Person.EmailAddress.BusinessEntityID;
  
    -- Set flag to indicate success
    SET @UpdateSuccessful = 1;
END;

Finally, if no updatable columns were specified by the UPDATE statement that fired the trigger, an error is raised and the transaction is rolled back:

-- If the update was not successful, raise an error and roll back the
-- transaction
IF @UpdateSuccessful = 1
    RAISERROR('Must specify updatable columns.', 10, 127);

Listing 6-12 demonstrates a simple UPDATE against the Sales.vIndividualCustomer view with the INSTEAD OF trigger from Listing 6-11 created on it. The result is shown in Figure 6-5.

Listing 6-12.  Updating a View Through an INSTEAD OF Trigger

UPDATE Sales.vIndividualCustomer
SET FirstName = N'Dave',
    MiddleName = N'Robert',
    EmailAddress = N'[email protected]'
WHERE BusinessEntityID = 1699;
  
SELECT BusinessEntityID, FirstName, MiddleName, LastName, EmailAddress
FROM Sales.vIndividualCustomer

WHERE BusinessEntityID = 1699;