This chapter presents recipes for creating and using Data Manipulation Language (DML) and Data Definition Language (DDL) triggers. DML triggers respond to INSERT, UPDATE, and DELETE operations against tables and views. DDL triggers respond to server and database events such as CREATE TABLE and DROP TABLE statements.

Triggers, when used properly, can provide a convenient automatic response to specific actions. They are appropriate for situations in which you must create a business-level response to an action. However, they should not be used in place of constraints, such as primary key, foreign key, and check and unique constraints, because constraints will outperform triggers and are better suited to these operations.

Remember that the code inside a trigger executes in response to an action. A user may be attempting to update a table, and the trigger code executes—in many cases, unknown to the user who executed the update. If trigger code is not optimized properly, the triggers may have a severe impact on system performance. Use DML triggers sparingly, and take care to ensure that they are optimized and bug-free.

DDL triggers open a realm of new functionality for monitoring and auditing server activity that cannot be easily replaced by other database object types.

This chapter will cover the following topics:

• How to create an AFTER DML trigger
• How to create an INSTEAD OF DML trigger
• How to create a DDL trigger
• How to modify or drop an existing trigger
• How to enable or disable triggers
• How to limit trigger nesting, set the firing order, and control recursion
• How to view trigger metadata
• How to use triggers to respond to logon events

First, however, I’ll start with a background discussion of DML triggers.

20-1. Creating an AFTER DML Trigger

Problem

You want to track the inserts and deletes from your production inventory table. A number of applications access this table, and you cannot dictate that these applications use a common set of stored procedures to access the table.

Solution

DML triggers respond to INSERT, UPDATE, or DELETE operations against a table or a view. When a data modification event occurs, the trigger performs a set of actions defined by that trigger. Similar to stored procedures, triggers are defined as a batch of Transact-SQL statements.

A DML trigger may be declared specifically as FOR UPDATE, FOR INSERT, FOR DELETE, or any combination of the three. UPDATE triggers respond to data modified in one or more columns within the table, INSERT triggers respond to new data being added to a table, and DELETE triggers respond to data being deleted from a table. There are two types of DML triggers: AFTER and INSTEAD OF.

AFTER triggers are allowed only for tables, and they execute after the data modification has been completed against the table. INSTEAD OF triggers execute instead of the original data modification and can be created for both tables and views.

DML triggers perform actions in response to data modifications. For example, a trigger could populate an audit table based on an operation performed. Or perhaps a trigger could be used to decrement the value of an inventory quantity in response to a sales transaction. Though the ability to trigger actions automatically is a powerful feature, there are a few things to keep in mind when deciding to use a trigger to perform application or business logic.

• Triggers are often forgotten about and therefore become a hidden problem. When troubleshooting performance or logical issues, DBAs and developers often forget that triggers are executing in the background. Make sure that the use of triggers is “visible” in data and application documentation.
• If all data modifications flow through a stored procedure, a set of stored procedures, or even a common data-access layer, then perform all activities within the stored-procedure layer or data-access layer rather than use a trigger. For example, if an inventory quantity should be updated after inserting a sales record, attempt to put the logic within the stored procedure.
• Always keep performance in mind: Write triggers that execute quickly. Long-running triggers can significantly impact data-modification operations. Take particular care in putting triggers onto tables that are subject to either a high rate of data modification or data modifications that affect a large number of rows.
• Nonlogged updates do not cause a DML trigger to fire (for example, WRITETEXT, TRUNCATE TABLE, and bulk insert operations).
• Constraints usually run faster than a DML trigger, so if business requirements can be modeled using constraints, then use constraints instead of triggers.
• AFTER triggers run after the data modification has occurred, so they cannot be used to alter data modification to prevent constraint violations.
• Don’t allow result sets from a SELECT statement to be returned within your trigger. Most applications cannot consume results from a trigger, and embedded queries may hurt the trigger’s performance.

As long as you keep these general guidelines in mind and use them properly, triggers are an excellent means of enforcing business rules in your database.

An AFTER DML trigger can track the changes to the ProductionInventory table. This recipe creates an AFTER DML trigger that executes when INSERT and DELETE statements are executed against the table.

An AFTER DML trigger executes after an INSERT, UPDATE, and/or DELETE modification has been completed successfully against a table. The specific syntax for an AFTER DML trigger is as follows:

CREATE TRIGGER [schema_name.]trigger_name
ON table
[WITH <dml_trigger_option> [...,n]]
AFTER
{[INSERT][,] [UPDATE] [,][DELETE]}
[NOT FOR REPLICATION]
AS {sql_statement[...n]}

Table 20-1 details the arguments of this command.

Before proceeding to the recipe, it is important to note that SQL Server creates two “virtual” tables that are available specifically for triggers, called the inserted and deleted tables. These two tables capture the before and after pictures of the modified rows. Table 20-2 shows the tables that each DML operation impacts.

The inserted and deleted tables can be used within your trigger to access the versions of data both before and after the data modifications. These tables store data for both single and multi-row updates. Triggers should be coded with both types of updates (single and multi-row) in mind. For example, a DELETE statement may impact either a single row or many, say 50, rows, And the trigger must handle both cases appropriately.

This recipe demonstrates how to use a trigger to track row inserts or deletes from the Production.ProductInventory table:

-- Create a table to Track all Inserts and Deletes
USE AdventureWorks2014;
GO

CREATE TABLE Production.ProductInventoryAudit
       (
        ProductID INT NOT NULL,
        LocationID SMALLINT NOT NULL,
        Shelf NVARCHAR(10) NOT NULL,
        Bin TINYINT NOT NULL,
        Quantity SMALLINT NOT NULL,
        rowguid UNIQUEIDENTIFIER NOT NULL,
        ModifiedDate DATETIME NOT NULL,
        InsertOrDelete CHAR(1) NOT NULL
       );
GO
-- Create trigger to populate Production.ProductInventoryAudit table
CREATE TRIGGER Production.trg_id_ProductInventoryAudit ON Production.ProductInventory
       AFTER INSERT, DELETE
AS
BEGIN
       SET NOCOUNT ON;
-- Inserted rows
       INSERT INTO Production.ProductInventoryAudit
                (ProductID,
                 LocationID,
                 Shelf,
                 Bin,
                 Quantity,
                 rowguid,
                 ModifiedDate,
                 InsertOrDelete)
                SELECT
                        i.ProductID,
                        i.LocationID,
                        i.Shelf,
                        i.Bin,
                        i.Quantity,
                        i.rowguid,
                        GETDATE(),
                        'I'
                FROM    inserted i
                UNION ALL
                SELECT  d.ProductID,
                        d.LocationID,
                        d.Shelf,
                        d.Bin,
                        d.Quantity,
                        d.rowguid,
                        GETDATE(),
                        'D'
                FROM    deleted d;

END
GO

-- Insert a new row
INSERT INTO Production.ProductInventory
        (ProductID,
         LocationID,
         Shelf,
         Bin,
         Quantity)
VALUES  (316,
         6,
         'A',
         4,
         22);

-- Delete a row
DELETE
FROM Production.ProductInventory
WHERE   ProductID = 316
        AND LocationID = 6;

-- Check the audit table
SELECT  ProductID,
        LocationID,
        InsertOrDelete
FROM    Production.ProductInventoryAudit;

This returns the following:

ProductID   LocationID InsertOrDelete
----------- ---------- --------------
316         6          I
316         6          D

How It Works

This recipe started by creating a new table for tracking rows inserted into or deleted from the Production.ProductInventory table. The new table’s schema matches the original table, but the table has added a new column named InsertOrUpdate to indicate whether the change was because of an INSERT or DELETE operation. See the following:

CREATE TABLE Production.ProductInventoryAudit
       (
        ProductID INT NOT NULL,
        LocationID SMALLINT NOT NULL,
        Shelf NVARCHAR(10) NOT NULL,
        Bin TINYINT NOT NULL,
        Quantity SMALLINT NOT NULL,
        rowguid UNIQUEIDENTIFIER NOT NULL,
        ModifiedDate DATETIME NOT NULL,
        InsertOrDelete CHAR(1) NOT NULL
       );
GO

Next, an AFTER DML trigger was created using CREATE TRIGGER. The schema and name of the new trigger were designated in the first line of the statement:

CREATE TRIGGER Production.trg_id_ProductInventoryAudit

The table (which when updated will cause the trigger to fire) was designated in the ON clause:

ON Production.ProductInventory

Two types of DML activity were set to be monitored: inserts and deletes:

AFTER INSERT, DELETE

The body of the trigger began after the AS keyword:

AS
BEGIN

The SET NOCOUNT was set ON in order to suppress the “rows affected” messages from being returned to the calling application whenever the trigger is fired:

       SET NOCOUNT ON;

The trigger contained one INSERT statement of the form INSERT INTO ... SELECT, where the SELECT statement was a UNION of two selects. The first SELECT in the UNION returned the rows from the INSERTED table, and the second SELECT in the union returned rows from the DELETED table.

First, we set up the INSERT statement and specified the table into which the statement was to insert rows, as well as the columns that should be specified for each row that was being inserted:

       INSERT INTO Production.ProductInventoryAudit
                (ProductID,
                 LocationID,
                 Shelf,
                 Bin,
                 Quantity,
                 rowguid,
                 ModifiedDate,
                 InsertOrDelete)

Next, we selected rows from the INSERTED table (this is a list of rows that were inserted into the ProductInventory table) and specified the columns that were to be mapped to the INSERT clause:

                SELECT i.ProductID,
                        i.LocationID,
                        i.Shelf,
                        i.Bin,
                        i.Quantity,
                        i.rowguid,
                        GETDATE(),
                        'I'
                FROM    inserted i

The second select returned rows from the DELETED table and concatenated the results with a UNION ALL. The DELETED table contains a list of rows that were deleted from the ProductInventory table:

                UNION ALL
                SELECT  d.ProductID,
                        d.LocationID,
                        d.Shelf,
                        d.Bin,
                        d.Quantity,
                        d.rowguid,
                        GETDATE(),
                        'D'
                FROM    deleted d;
        END
GO

After creating the trigger, in order to test it, a new row was inserted into and then deleted from Production.ProductInventory:

INSERT INTO Production.ProductInventory
        (ProductID,
         LocationID,
         Shelf,
         Bin,
         Quantity)
VALUES  (316,
         6,
         'A',
         4,
         22);

-- Delete a row
DELETE
FROM Production.ProductInventory
WHERE   ProductID = 316
        AND LocationID = 6;

A query executed against the audit table shows two rows tracking the insert and delete activities against the Production.ProductInventory table:

SELECT  ProductID,
        LocationID,
        InsertOrDelete
FROM    Production.ProductInventoryAudit;

20-2. Creating an INSTEAD OF DML Trigger

Problem

You have a table that contains a list of departments for your human resources group. An application needs to insert new departments. However, new departments should be routed to a separate table that holds these departments “pending approval” by a separate application function. You want to create a view that concatenates the “approved” and “pending approval” departments. You also want to allow the application to insert into this view any new departments that are added to the “pending approval” table.

Solution

An INSTEAD OF DML trigger allows data to be updated in a view that would otherwise not be updateable. The code inside the INSTEAD OF DML trigger executes instead of the original data modification statement. These triggers are allowed on both tables and views and are often used to handle data modifications to views that do not usually allow data modifications (see Chapter 14 for a review of what rules a view must follow in order to be updateable). INSTEAD OF DML triggers use the following syntax:

CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view }
[ WITH <dml_trigger_option> [ ...,n ] ] INSTEAD OF

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ...n ] }

Table 20-3 details the arguments of this command.

This recipe creates a new table that holds “pending approval” rows for the HumanResources.Department table. These are new departments that require manager approval before being added to the actual table. A view is created to display all “approved” and “pending approval” departments from the two tables, and an INSTEAD OF trigger is created for inserts on the view. This INSTEAD OF trigger allows the developer to define an action to take instead of the INSERT statement. In this case, the trigger will insert the data into one of the base tables of the view—HumanResources.Department. See the following:

USE AdventureWorks2014;
GO

-- Create Department "Approval" table
CREATE TABLE HumanResources.DepartmentApproval
       (
        Name NVARCHAR(50) NOT NULL
                          UNIQUE,
        GroupName NVARCHAR(50) NOT NULL,
        ModifiedDate DATETIME NOT NULL
                              DEFAULT GETDATE()
       ) ;
GO
-- Create view to see both approved and pending approval departments
CREATE VIEW HumanResources.vw_Department
AS
       SELECT   Name,
                GroupName,
                ModifiedDate,
                'Approved' Status
       FROM     HumanResources.Department
       UNION
       SELECT   Name,
                GroupName,
                ModifiedDate,
                'Pending Approval' Status
       FROM     HumanResources.DepartmentApproval ;
GO

-- Create an INSTEAD OF trigger on the new view
CREATE TRIGGER HumanResources.trg_vw_Department ON HumanResources.vw_Department
       INSTEAD OF INSERT
AS
       SET NOCOUNT ON
       INSERT INTO HumanResources.DepartmentApproval
                (Name,
                 GroupName)
                SELECT  i.Name,
                        i.GroupName
                FROM    inserted i
                WHERE   i.Name NOT IN (
                        SELECT  Name
                        FROM    HumanResources.DepartmentApproval) ;
GO

-- Insert into the new view, even though view is a UNION
-- of two different tables
INSERT INTO HumanResources.vw_Department
        (Name,
         GroupName)
VALUES  ('Print Production',
         'Manufacturing') ;

-- Check the view's contents
SELECT  Status,
        Name
FROM    HumanResources.vw_Department
WHERE   GroupName = 'Manufacturing' ;

This returns the following result set:

Status             Name
----------------   --------------------------------------------------
Approved           Production
Approved           Production Control
Pending Approval   Print Production

How It Works

The recipe began by creating a separate table to hold “pending approval” department rows:

CREATE TABLE HumanResources.DepartmentApproval
       (
        Name NVARCHAR(50) NOT NULL
                          UNIQUE,
        GroupName NVARCHAR(50) NOT NULL,
        ModifiedDate DATETIME NOT NULL
                              DEFAULT GETDATE()
       ) ;
GO

Next, a view was created to display both “approved” and “pending approval” departments:

CREATE VIEW HumanResources.vw_Department
AS
       SELECT   Name,
                GroupName,
                ModifiedDate,
                'Approved' Status
       FROM     HumanResources.Department
       UNION
       SELECT   Name,
                GroupName,
                ModifiedDate,
                'Pending Approval' Status
       FROM     HumanResources.DepartmentApproval ;
GO

The UNION in the CREATE VIEW prevented this view from being updateable. INSTEAD OF triggers allowed data modifications against nonupdateable views.

A trigger was created to react to INSERTs against the view, and it inserted the specified data into the approval table as long as the department name did not already exist in the HumanResources.DepartmentApproval table:

CREATE TRIGGER HumanResources.trg_vw_Department ON HumanResources.vw_Department
       INSTEAD OF INSERT
AS
       SET NOCOUNT ON;
       INSERT INTO HumanResources.DepartmentApproval
                (Name,
                 GroupName)
                SELECT  i.Name,
                        i.GroupName
                FROM    inserted i
                WHERE   i.Name NOT IN (
                        SELECT  Name
                        FROM    HumanResources.DepartmentApproval) ;
GO

A new INSERT was tested against the view to see whether it was inserted in the approval table:

INSERT INTO HumanResources.vw_Department
        (Name,
         GroupName)
VALUES  ('Print Production',
         'Manufacturing') ;m

We then queried the view to show that the row was inserted and displayed a “pending approval” status:

SELECT  Status,
        Name
FROM    HumanResources.vw_Department
WHERE   GroupName = 'Manufacturing';

20-3. Handling Transactions in Triggers

Problem

You have been viewing the ProductInventory changes that are tracked in your ProductInventoryAudit table. You notice that some applications are violating business rules, and this breaks other applications that are using the ProductInventory table. You want to prevent these changes and roll back the transaction that violates the business rules.

Solution

When a trigger is fired, SQL Server always creates a transaction around it. This allows any changes made by the firing trigger, or the caller, to be rolled back to the previous state. In this example, the trg_uid_ ProductlnventoryAudit trigger has been rewritten to fail if certain Shelf or Quantity values are encountered. If they are, ROLLBACK is used to cancel the trigger and reverse any changes.

USE AdventureWorks2014;
GO

ALTER TRIGGER Production.trg_id_ProductInventoryAudit ON Production.ProductInventory
       AFTER INSERT, DELETE
AS
       SET NOCOUNT ON ;
       IF EXISTS ( SELECT   Shelf
                   FROM     inserted
                   WHERE    Shelf = 'A' )
          BEGIN
                PRINT 'Shelf ''A'' is closed for new inventory.' ;
                ROLLBACK ;
          END
-- Inserted rows
       INSERT INTO Production.ProductInventoryAudit
                (ProductID,
                 LocationID,
                 Shelf,
                 Bin,
                 Quantity,
                 rowguid,
                 ModifiedDate,
                 InsertOrDelete)
                SELECT DISTINCT
                        i.ProductID,
                        i.LocationID,
                        i.Shelf,
                        i.Bin,
                        i.Quantity,
                        i.rowguid,
                        GETDATE(),
                        'I'
                FROM    inserted i ;
-- Deleted rows
       INSERT INTO Production.ProductInventoryAudit
                (ProductID,
                 LocationID,
                 Shelf,
                 Bin,
                 Quantity,
                 rowguid,
                 ModifiedDate,
                 InsertOrDelete)
                SELECT  d.ProductID,
                        d.LocationID,
                        d.Shelf,
                        d.Bin,
                        d.Quantity,
                        d.rowguid,
                        GETDATE(),
                        'D'
                FROM    deleted d ;
       IF EXISTS ( SELECT   Quantity
                   FROM     deleted
                   WHERE    Quantity > 0 )
          BEGIN
                PRINT 'You cannot remove positive quantity rows!' ;
                ROLLBACK ;
          END
GO

/* Attempt an insert of a row using Shelf A */

INSERT INTO Production.ProductInventory
        (ProductID,
         LocationID,
         Shelf,
         Bin,
         Quantity)
VALUES  (316,
         6,
         'A',
         4,
         22) ;

Because this is not allowed based on the trigger logic, the trigger neither inserts a row into the ProductInventoryAudit table nor allows the INSERT into the ProductInventory table. The following is returned as a result of the INSERT statement:

Shelf 'A' is closed for new inventory.
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

In this example, the INSERT that caused the trigger to fire didn’t use an explicit transaction; however, the operation was still rolled back. This next example demonstrates two deletions: one that is allowed (according to the rules of the trigger) and another that is not allowed. Both inserts are embedded in an explicit transaction:

BEGIN TRANSACTION ;
-- Deleting a row with a zero quantity
DELETE
FROM Production.ProductInventory
WHERE   ProductID = 853
        AND LocationID = 7 ;
-- Deleting a row with a non-zero quantity
DELETE
FROM Production.ProductInventory
WHERE   ProductID = 999
        AND LocationID = 60 ;
COMMIT TRANSACTION ;

This returns the following:

(1 row(s) affected)
You cannot remove positive quantity rows!
Msg 3609, Level 16, State 1, Line 9
The transaction ended in the trigger. The batch has been aborted.

Because the trigger issued a rollback, the transaction that caused the trigger to fire is rolled back. Even though the first row would have been a valid deletion, neither row is deleted because they were in the same calling transaction.

SELECT  ProductID,
        LocationID
FROM    Production.ProductInventory
WHERE   (ProductID = 853
         AND LocationID = 7)
        OR (ProductID = 999
            AND LocationID = 60) ;

This returns the following:

How It Works

This recipe demonstrated the interaction between triggers and transactions. If a trigger issues a ROLLBACK, any data modifications  performed by the trigger or the statements in the calling transaction are undone. The Transact-SQL query or batch that invoked the trigger is canceled and rolled back. If you use explicit transactions within a trigger, SQL Server will treat it as a nested transaction. As discussed in Chapter 12, a ROLLBACK rolls back all transactions, no matter how many levels deep they may be nested.

20-4. Linking Trigger Execution to Modified Columns

Problem

You want to prevent updates to one column in a specific table.

Solution

When a trigger is fired, you can determine which columns have been modified by using the UPDATE function.

UPDATE, not to be confused with the DML command, returns a TRUE value if an INSERT or DML UPDATE references a column. For example, the following DML UPDATE trigger checks to see whether a specific column has been modified and, if so, returns an error and rolls back the modification:

USE AdventureWorks2014;
GO
CREATE TRIGGER HumanResources.trg_U_Department ON HumanResources.Department
       AFTER UPDATE
AS
       IF UPDATE(GroupName)
          BEGIN
                PRINT 'Updates to GroupName require DBA involvement.' ;
                ROLLBACK  ;
          END
GO

An attempt is made to update a GroupName value in the following query:

UPDATE  HumanResources.Department
SET     GroupName = 'Research and Development'
WHERE   DepartmentID = 10 ;

This returns a warning message and error telling us that the batch has been aborted (no updates made).

Updates to GroupName require DBA involvement.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

How It Works

When trigger logic is aimed at more-granular, column-based changes, use the UPDATE function and conditional processing to ensure that the code is executed only against specific columns. Embedding the logic in conditional processing can help reduce the overhead each time the trigger fires.

20-5. Viewing DML Trigger Metadata

Problem

You have a number of DML triggers defined in your database, and you want to list these triggers and the objects on which they are defined.

Solution

This recipe demonstrates how to view information about the triggers in the current database.

The first example queries the sys.triggers catalog view and returns the name of the view or table, the trigger name, whether the trigger is an INSTEAD OF trigger, and whether the trigger is disabled:

-- Show the DML triggers in the current database
USE AdventureWorks2014;
GO

SELECT  OBJECT_NAME(parent_id) AS ParentObjName,
        name AS TriggerName,
        is_instead_of_trigger,
        is_disabled
FROM    sys.triggers t
WHERE   parent_class_desc = 'OBJECT_OR_COLUMN'
ORDER BY OBJECT_NAME(parent_id),Name ;

This returns the following results (your results may vary slightly depending on what triggers you have defined):

ParentObjName     TriggerName                      is_instead_of_trigger is_disabled
------------------- ------------------------------ --------------------- -----------
Department          trg_U_Department               0                     0
Employee            dEmployee                      1                     0
Person              iuPerson                       0                     0
ProductInventory    trg_uid_ProductInventoryAudit  0                     0
PurchaseOrderDetail iPurchaseOrderDetail           0                     0
PurchaseOrderDetail uPurchaseOrderDetail           0                     0
PurchaseOrderHeader uPurchaseOrderHeader           0                     0
SalesOrderDetail    iduSalesOrderDetail            0                     0
SalesOrderHeader    uSalesOrderHeader              0                     0
Vendor              dVendor                        1                     0
vw_Department       trg_vw_Department              1                     0
WorkOrder           iWorkOrder                     0                     0
WorkOrder           uWorkOrder                     0                     0

To display a specific trigger’s Transact-SQL definition, you can query the sys.sql_modules system catalog view:

-- Displays the trigger SQL definition --(if the trigger is not encrypted)
USE AdventureWorks2014;
GO

SELECT  o.name
        , (SELECT definition AS [processing-instruction(definition)]
            FROM sys.sql_modules
            WHERE object_id = m.object_id
            FOR XML PATH(''), TYPE
      ) AS TrigDefinition
FROM    sys.sql_modules m
        INNER JOIN sys.objects o
            ON m.object_id = o.object_id
WHERE   o.type = 'TR'
AND o.name = 'trg_id_ProductInventoryAudit';

How It Works

The first query in this recipe queried the sys.triggers catalog view to show all the DML triggers in the current database. There were DDL triggers in the sys.triggers catalog view as well. To prevent DDL triggers from being displayed in the results, the query filtered on the type column from the sys.objects catalog view, looking for the value ‘T.R.’ DDL triggers have a different parent class, as we will discuss in Recipe 20-8.

The second query showed the actual Transact-SQL trigger name and definition of each trigger in the database. If the trigger were encrypted (similar to an encrypted view or stored procedure, for example), the trigger definition would be displayed as NULL.

20-6. Creating a DDL Trigger

Problem

You are testing index changes in a system and want to log any such changes so that you can correlate the index change with performance data that you are capturing on the server.

Solution

DDL triggers respond to server or database events rather than to table data modifications. For example, a DDL trigger could write to an audit table whenever a database user issues a CREATE TABLE or DROP TABLE statement. Or, at the server level, a DDL trigger could respond to the creation of a new login and prevent that login from being created, or simply log the activity.

DDL triggers may be defined as database; or server triggers. Database DDL triggers are stored as objects within the database in which they were created, and server DDL triggers are stored in the master database. The syntax for a DDL trigger is as follows:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ...,n ] ]
FOR { event_type | event_group } [ ,...n ]
AS { sql_statement [ ...n ]}

Table 20-4 details the arguments of this command.

This recipe demonstrates how to create an audit table that can contain information on any CREATE INDEX, ALTER INDEX, or DROP INDEX statements in the AdventureWorks2014 database.

First, create an audit table to hold the results:

USE AdventureWorks2014;
GO

CREATE TABLE dbo.DDLAudit
              (
              EventData XML NOT NULL,
              AttemptDate DATETIME NOT NULL
                          DEFAULT GETDATE(),
              DBUser sysname NOT NULL
              ) ;
GO

Next, create a database DDL trigger to track index operations and insert the event data into the audit table:

CREATE TRIGGER db_trg_INDEXChanges ON DATABASE
       FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
       SET NOCOUNT ON ;
       INSERT INTO dbo.DDLAudit
                (EventData, DBUser)
       VALUES   (EVENTDATA(), USER) ;
GO

Next, attempt an index creation in the database:

CREATE NONCLUSTERED INDEX ni_DDLAudit_DBUser
        ON dbo.DDLAudit(DBUser) ;
GO

Next, I’ll query the DDLAudit audit table to see whether the new index-creation event was captured by the trigger:

SELECT  EventData
FROM    dbo.DDLAudit

This returns the actual event information, stored in XML format (see Chapter 26 for more information on XML in SQL Server).

EventData
----------------------------------------------------------------

<EVENT_INSTANCE>
  <EventType>CREATE_INDEX</EventType>
  <PostTime>2015-02-08T15:29:57.153</PostTime>
  <SPID>59</SPID>
  <ServerName>ROMEForever</ServerName>
  <LoginName>ROMEForeverjason</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks2014</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>ni_DDLAudit_DBUser</ObjectName>
  <ObjectType>INDEX</ObjectType>
  <TargetObjectName>DDLAudit</TargetObjectName>
  <TargetObjectType>TABLE</TargetObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE NONCLUSTERED INDEX ni_DDLAudit_DBUser
        ON dbo.DDLAudit(DBUser)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

How It Works

The recipe began by creating a table that could contain audit information on index modifications. The EventData column used SQL Server’s xml data type, which was populated by the new EVENTDATA function (described later in this recipe). See the following:

CREATE TABLE dbo.DDLAudit
              (
             EventData XML NOT NULL,
             AttemptDate DATETIME NOT NULL
                               DEFAULT GETDATE(),
             DBUser CHAR(50) NOT NULL
             ) ;
GO

The DDL trigger was created to subscribe to CREATE INDEX, ALTER INDEX, or DROP INDEX statements:

CREATE TRIGGER db_trg_INDEXChanges ON DATABASE
       FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS

The SET NOCOUNT statement was used in the trigger to suppress the number of row-affected messages from SQL Server (otherwise, every time you made an index modification, you’d see a “1 row affected” message):

       SET NOCOUNT ON ;

A row was inserted into the audit table containing the event data and info on the user who performed the statement that fired the event:

       INSERT INTO dbo.ChangeAttempt
                (EventData, DBUser)
       VALUES   (EVENTDATA(), USER) ;
GO

The EVENTDATA function returned server and data event information in XML format. The XML data returned from the EVENTDATA function included useful information such as the event statement text, the login name that attempted the statement, the target object name, and the time the event occurred. For more information about the EVENTDATA function, please refer to SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms187909.aspx).

20-7. Creating a Logon Trigger

Problem

You want to restrict the times at which certain users can log in to your database server. If an attempt is made to log in during incorrect hours, you want to log that attempt to an audit table.

Solution

Logon triggers fire synchronously in response to a logon event to the SQL Server instance. You can use logon triggers to create reactions to specific logon events, or simply to track information about a logon event.

This recipe demonstrates how to create a logon trigger that restricts a login from accessing SQL Server during certain time periods. The example will also log any invalid logon attempts to a table.

First, create the new login:

CREATE LOGIN nightworker WITH PASSWORD = 'pass@word1' ;
GO

Next, create an audit database and a table to track the logon attempts:

CREATE DATABASE ExampleAuditDB ;
GO
USE ExampleAuditDB ;
GO
CREATE TABLE dbo.RestrictedLogonAttempt
       (
        LoginName SYSNAME NOT NULL,
        AttemptDate DATETIME NOT NULL
       ) ;
GO

Create the logon trigger to restrict the new login from logging into the server from 7 a.m. to 6 p.m.:

USE master ;
GO
CREATE TRIGGER trg_logon_attempt ON ALL SERVER
 WITH EXECUTE AS 'sa'
       FOR LOGON
AS
       BEGIN
             IF ORIGINAL_LOGIN() = 'nightworker'
                AND DATEPART(hh, GETDATE()) BETWEEN 7 AND 18
                BEGIN
                      ROLLBACK ;
                      INSERT    ExampleAuditDB.dbo.RestrictedLogonAttempt
                                (LoginName, AttemptDate)
                      VALUES    (ORIGINAL_LOGIN(), GETDATE()) ;
                END
       END
GO

Now, attempt to log on as the nightworker login with the password pass@word1 during the specified time range. The login attempt should yield the following error message:

Logon failed for login 'nightworker' due to trigger execution.

After the attempt, query the audit table to see whether the logon was tracked:

SELECT  LoginName, AttemptDate
FROM    ExampleAuditDB.dbo.RestrictedLogonAttempt;

This returns the following result set (results will vary based on when you execute this recipe).

LoginNM                AttemptDT
---------------------- -----------------------
nightworker            2015-01-01 03:20:19.577

How It Works

Logon triggers allow you to restrict and track logon activity after authentication to the SQL Server instance but before an actual session is generated. If you want to apply custom business rules to logons above and beyond what is offered within the SQL Server feature set, you can implement them using the logon trigger.

This recipe created a test login, a new auditing database, and an auditing table to track attempts. The logon trigger was created in the master database. Stepping through the code, note that ALL SERVER was used to set the scope of the trigger execution; this is a server DDL trigger as opposed to a database DDL trigger:

CREATE TRIGGER trg_logon_attempt ON ALL SERVER

The EXECUTE AS clause was used to define the permissions under which the trigger will execute. The recipe could have used a lower-privileged login—any login with permission to write to the login table would suffice:

WITH EXECUTE AS 'sa'

FOR LOGON designated the event that this trigger subscribes to:

       FOR LOGON
AS

The body of the trigger logic then started at the BEGIN keyword:

BEGIN

The original security context of the logon attempt was then evaluated. In this case, the trigger is interested in enforcing logic only if the login is for nightworker:

             IF ORIGINAL_LOGIN() = 'nightworker'

Included in this logic was an evaluation of the hour of the day. If the current time were between 7 a.m. and 6 p.m., two actions would be performed:

                AND DATEPART(hh, GETDATE()) BETWEEN 7 AND 18
                BEGIN

The first action would be to roll back the logon attempt:

                      ROLLBACK ;

The second action would be to track the attempt to the audit table:

                      INSERT    ExampleAuditDB.dbo.RestrictedLogonAttempt
                                (LoginName, AttemptDate)
                      VALUES    (ORIGINAL_LOGIN(), GETDATE()) ;
                END
       END
GO

Again, it is worthwhile to remind you that how you code the logic of a logon trigger is very important. Improper logging (e.g., logging before a rollback occurs, bad trigger logic, or poor-performing trigger code) can cause unexpected results. Also, if your logon trigger isn’t performing the actions you expect, be sure to check your latest SQL log for clues. Logon trigger attempts that are rolled back also get written to the SQL log. If something was miscoded in the trigger—for example, if I hadn’t designated the proper, fully qualified table name for RestrictedLogonAttempt—the SQL log would have shown the error message “Invalid object name ‘dbo.RestrictedLogonAttempt.’”

20-8. Viewing DDL Trigger Metadata

Problem

You want to list the server and database DDL triggers that are defined on your server.

Solution

This recipe demonstrates the retrieval of DDL trigger metadata.

The first example queries the sys.triggers catalog view, returning the associated database-scoped trigger name and the trigger’s enabled/disabled status:

USE AdventureWorks2014;
GO

-- Show the DML triggers in the current database
SELECT  name AS TriggerName,
        is_disabled
FROM    sys.triggers
WHERE   parent_class_desc = 'DATABASE'
ORDER BY  Name;

This returns the following results:

TriggerNM                is_disabled
---------------------    -----------
ddlDatabaseTriggerLog    1

This next example queries the sys.server_triggers and sys.server_trigger_events system catalog views to retrieve a list of server-scoped DDL triggers. This returns the name of the DDL trigger, the type of trigger (Transact-SQL or CLR), the disabled state of the trigger, and the events the trigger subscribed to:

SELECT  name AS TriggerName,
        s.type_desc AS TriggerType,
        is_disabled,
        e.type_desc AS FiringEvents
FROM    sys.server_triggers s
        INNER JOIN sys.server_trigger_events e
            ON s.object_id = e.object_id;

This returns data based on the server-level trigger created previously.

name                     S0L_or_CLR    is_disabled FiringEvents
------------------------ ------------- ----------- --------------
trg_logon_attempt        SQL_TRIGGER   1           LOGON

To display database-scoped DDL trigger Transact-SQL definitions, you can query the sys.sql_modules system catalog view:

USE AdventureWorks2014;
GO

SELECT  t.name AS TriggerName
        , (SELECT definition AS [processing-instruction(definition)]
            FROM sys.sql_modules
            WHERE object_id = t.object_id
            FOR XML PATH(''), TYPE
      ) AS TrigDefinition
FROM    sys.triggers AS t
WHERE   t.parent_class_desc = 'DATABASE';

To display server-scoped DDL triggers, you can query the sys.server_sql_modules and sys.server_triggers system catalog views:

USE master;
GO
SELECT  t.name
        , (SELECT definition AS [processing-instruction(definition)]
            FROM sys.server_sql_modules
            WHERE object_id = t.object_id
            FOR XML PATH(''), TYPE
      ) AS TrigDefinition
FROM   sys.server_triggers t;

How It Works

The first query in this recipe returned a list of database-scoped triggers using the sys.triggers system catalog view. To display only DDL database-scoped triggers, the query filtered the parent_class_desc value to DATABASE. The second query returned a list of server-scoped triggers and their associated triggering events. These triggers were accessed through the sys.server_triggers and sys.server_trigger_events system catalog views.

The third query returned the Transact-SQL definitions of database-scoped triggers through the sys.triggers and sys.sql_modules catalog views. In the final query, the sys.server_sql_modules and sys.server_triggers system catalog views were joined to return a server-scoped trigger’s Transact-SQL definitions.

20-9. Modifying a Trigger

Problem

You have an existing trigger and need to modify the trigger definition.

Solution

To modify an existing DDL or DML trigger, use the ALTER TRIGGER command. ALTER TRIGGER takes the same arguments as the associated DML or DDL CREATE TRIGGER syntax.

This example will modify the login trigger that was created in Recipe 20-7. The login trigger should no longer restrict users from logging in, but instead should allow the login and write the login only to the audit table.

The following statement modifies the login trigger. Note the rollback has been commented out:

USE master;
GO

ALTER TRIGGER trg_logon_attempt ON ALL SERVER
 WITH EXECUTE AS 'sa'
       FOR LOGON
AS
       BEGIN
             IF ORIGINAL_LOGIN() = 'nightworker'
                AND DATEPART(hh, GETDATE()) BETWEEN 7 AND 18
                BEGIN
                      --ROLLBACK ;
                      INSERT    ExampleAuditDB.dbo.RestrictedLogonAttempt
                                (LoginName, AttemptDate)
                      VALUES    (ORIGINAL_LOGIN(), GETDATE()) ;
                END
       END
GO

An attempt to log in to the server with the login nightworker and password pass@word1 should now be allowed, and you should see the login attempt recorded in the audit table:

SELECT  LoginName,
        AttemptDate
FROM    ExampleAuditDB.dbo.RestrictedLogonAttempt;

The preceding select statement returns the following:

LoginNM                AttemptDT
---------------------- -----------------------
nightworker            2014-01-01 012:20:19.577
nightworker            2014-01-02 14:20:33.577

How It Works

ALTER TRIGGER allows you to modify existing DDL or DML triggers. The arguments for ALTER TRIGGER are the same as for CREATE TRIGGER.

20-10. Enabling and Disabling a Trigger

Problem

You have a trigger defined for a table that you would like to disable temporarily, but you still want to keep the definition in the database so that you can easily reenable the trigger.

Solution

Sometimes triggers must be disabled if they are causing problems that you need to troubleshoot, or if you need to import or recover data that shouldn’t fire the trigger but might. In this recipe, I demonstrate how to disable a trigger from firing using the DISABLE TRIGGER command, as well as how to reenable a trigger using ENABLE TRIGGER.

The syntax for DISABLE TRIGGER is as follows:

DISABLE TRIGGER [ schema . ] trigger_name ON { object_name | DATABASE | ALL SERVER }

The syntax for enabling a trigger is as follows:

ENABLE TRIGGER [ schema_name . ] trigger_name ON { object_name | DATABASE | ALL SERVER }

Table 20-5 details the arguments of this command.

This example starts by creating a trigger (which is enabled by default) that prints a message that an INSERT has been performed against the HumanResources.Department table:

USE AdventureWorks2014;
GO

CREATE TRIGGER HumanResources.trg_Department ON HumanResources.Department
       AFTER INSERT
AS
       PRINT 'The trg_Department trigger was fired' ;
GO

Disable the trigger using the DISABLE TRIGGER command:

USE AdventureWorks2014;
GO

DISABLE TRIGGER HumanResources.trg_Department
ON HumanResources.Department;

Because the trigger was disabled, no printed message will be returned when the following INSERT is executed:

INSERT  HumanResources.Department
        (Name,
         GroupName)
VALUES  ('Construction',
         'Building Services'),

This returns the following:

(1 row(s) affected)

Next, the trigger is enabled using the ENABLE TRIGGER command:

USE AdventureWorks2014;
GO

ENABLE TRIGGER HumanResources.trg_Department
        ON HumanResources.Department;

Now, when another INSERT is attempted, the trigger will fire, returning a message to the connection:

INSERT  HumanResources.Department
        (Name, GroupName)
VALUES  ('Cleaning', 'Building Services'),

This returns the following:

The trg_Department trigger was fired
(1 row(s) affected)

How It Works

This recipe started by creating a new trigger that printed a statement whenever a new row was inserted into the HumanResources.Department table.

After creating the trigger, the DISABLE TRIGGER command was used to keep it from firing (although the trigger’s definition still stayed in the database):

DISABLE TRIGGER HumanResources.trg_Department
ON HumanResources.Department;

An insert was performed that did not fire the trigger. The ENABLE TRIGGER command was then executed, and then another insert was attempted; this time, the INSERT fired the trigger.

20-11. Nesting Triggers

Problem

Your trigger inserts data into another table that has triggers defined. You want to control whether the data modifications performed in a trigger will cause additional triggers to fire.

Solution

Trigger nesting occurs when a trigger is fired, that trigger performs some DML, and that DML in turn fires another trigger. Depending on a given database schema and a group’s coding standards, this may or may not be a desirable behavior.

The SQL Server instance may be configured to either allow or disallow trigger nesting. Disabling the nested triggers option prevents any AFTER trigger from causing the firing of another trigger.

This example demonstrates how to disable or enable this behavior:

USE master ;
GO
-- Disable nesting
EXEC sp_configure 'nested triggers', 0 ;
RECONFIGURE WITH OVERRIDE ;
GO
-- Enable nesting
EXEC sp_configure 'nested triggers', 1 ;
RECONFIGURE WITH OVERRIDE ;
GO

This returns the following:

Configuration option 'nested triggers' changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option 'nested triggers' changed from 0 to 1. Run the RECONFIGURE statement to install.

How It Works

This recipe used the sp_configure system-stored procedure to change the nested trigger behavior at the server level. To disable nesting altogether, sp_configure was executed for the nested trigger server option, followed by the parameter 0, which disabled nesting:

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

Because server options contain both a current configuration as well as an actual runtime configuration value, the RECONFIGURE WITH OVERRIDE command was used to update the runtime value so that it took effect right away.

To enable nesting again, this server option was set back to 1 in the second batch of the recipe.

20-12. Controlling Recursion

Problem

You have a table in which a data modification causes a trigger to execute and update the table on which that trigger is defined.

Solution

A specific case of trigger nesting is trigger recursion. Trigger nesting is considered to be recursive if the action performed when a trigger fires causes the same trigger to fire again. This may happen directly; for instance, a trigger is defined on a table, and that trigger executes DML back to the table on which it is defined. Or it may be indirect; for example, a trigger updates another table, and a trigger on that other table updates the original table.

Recursion may be either allowed or disallowed by configuring the RECURSIVE_TRIGGERS database option. If recursion is allowed, AFTER triggers are still limited by the 32-level nesting limit to prevent an infinite loop.

This example demonstrates enabling and disabling this option:

-- Allow recursion
ALTER DATABASE AdventureWorks2014
SET RECURSIVE_TRIGGERS ON ;

-- View the db setting
SELECT  is_recursive_triggers_on
FROM    sys.databases
WHERE   name = 'AdventureWorks2014' ;

-- Prevents recursion
ALTER DATABASE AdventureWorks2014
SET RECURSIVE_TRIGGERS OFF ;

-- View the db setting
SELECT  is_recursive_triggers_on
FROM    sys.databases
WHERE   name = 'AdventureWorks2014';

This returns the following:

is_recursive_triggers_on 1
is_recursive_triggers_on 0

How It Works

ALTER DATABASE was used to configure database-level options, including whether triggers were allowed to fire recursively within the database. The option was enabled by setting RECURSIVE_TRIGGERS ON:

ALTER DATABASE AdventureWorks2014
SET RECURSIVE_TRIGGERS ON ;

The option was then queried by using the sys.databases system catalog view, which showed the current database option in the is_recursive_triggers_on column (1 for on, 0 for off):

SELECT  is_recursive_triggers_on
FROM    sys.databases
WHERE   name = 'AdventureWorks2014' ;

The recipe then disabled trigger recursion by setting the option to be OFF, and confirmed this by selecting from the sys.databases view.

20-13. Specifying the Firing Order

Problem

Over time you have accumulated multiple triggers on the same table. You are concerned that the order in which the triggers execute is nondeterministic, and you are seeing inconsistent results from simple insert, update, and delete activity.

Solution

In general, triggers that react to the same event (or events) should be consolidated by placing all their business logic into just one trigger. This improves the manageability and supportability of the triggers. Also, this issue of determining and specifying trigger order is avoidable if the trigger logic is consolidated.

That said, situations arise in which multiple triggers may fire in response to the same DML or DDL action, and often the order in which they are fired is important. The system-stored procedure sp_settriggerorder allows you to specify trigger order.

The syntax for sp_settriggerorder is as follows:

sp_settriggerorder [ (@triggername = ] '[ triggerschema.]triggername' , [ (@order = ] 'value' , [ (@stmttype = ] 'statement_type' [ , [ (@namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Table 20-6 details the arguments of this command.

This recipe creates a test table and adds three DML INSERT triggers to it. sp_settriggerorder will then be used to define the execution order of the triggers. See the following:

USE AdventureWorks2014;
GO

CREATE TABLE dbo.TestTriggerOrder (TestID INT NOT NULL) ;
GO

CREATE TRIGGER dbo.trg_i_TestTriggerOrder ON dbo.TestTriggerOrder
       AFTER INSERT
AS
       PRINT 'I will be fired first.' ;
GO

CREATE TRIGGER dbo.trg_i_TestTriggerOrder2 ON dbo.TestTriggerOrder
       AFTER INSERT
AS
       PRINT 'I will be fired last.' ;
GO

CREATE TRIGGER dbo.trg_i_TestTriggerOrder3 ON dbo.TestTriggerOrder
       AFTER INSERT
AS
       PRINT 'I will be somewhere in the middle.' ;
GO

EXEC sp_settriggerorder 'trg_i_TestTriggerOrder', 'First', 'INSERT' ;
EXEC sp_settriggerorder 'trg_i_TestTriggerOrder2', 'Last', 'INSERT' ;

INSERT INTO dbo.TestTriggerOrder
        (TestID)
VALUES  (1);

This returns the following:

I will be fired first.
I will be somewhere in the middle.
I will be fired last.

How It Works

This recipe started by creating a single-column test table, and three DML INSERT triggers were added to it. Using sp_settriggerorder, the first and last triggers to fire were defined:

EXEC sp_settriggerorder 'trg_i_TestTriggerOrder', 'First', 'INSERT' ;
EXEC sp_settriggerorder 'trg_i_TestTriggerOrder2', 'Last', 'INSERT' ;

An INSERT was then executed against the table, and the trigger messages were returned in the expected order.

To reiterate this point, use a single trigger on a table when you can. If you must create multiple triggers of the same type, and one of your triggers contains ROLLBACK functionality if an error occurs, be sure to set the trigger that has the most likely chance of failing as the first trigger to execute. This way, only the first-fired trigger needs to be executed, preventing the other triggers from having to fire and then roll back transactions unnecessarily.

20-14. Dropping a Trigger

Problem

You are deploying a new version of your database schema, and DML is now executed through stored procedures. You have consolidated the business logic that was enforced by your triggers into these stored procedures; it is now time to drop the triggers.

Solution

The syntax for dropping a trigger differs by trigger type (DML or DDL). The syntax for dropping a DML trigger is as follows:

DROP TRIGGER schema_name.trigger_name [ ,...n ]

Table 20-7 details the argument of this command.

The syntax for dropping a DDL trigger is as follows:

DROP TRIGGER trigger_name [ ,...n ]
ON { DATABASE | ALL SERVER }

Table 20-8 details the arguments of this command.

In the case of both DDL and DML syntax statements, the [ ,... n ] syntax block indicates that more than one trigger can be dropped at the same time.

The following example demonstrates dropping both a DML and a DDL trigger:

-- Switch context back to the AdventureWorks2014 database
USE AdventureWorks2014 ;
GO
-- Drop a DML trigger
DROP TRIGGER dbo.trg_i_TestTriggerOrder ;
-- Drop multiple DML triggers
DROP TRIGGER dbo.trg_i_TestTriggerOrder2, dbo.trg_i_TestTriggerOrder3 ;
-- Drop a DDL trigger
DROP TRIGGER db_trg_INDEXChanges
ON DATABASE;

How It Works

In this recipe, DML and DDL triggers were explicitly dropped using the DROP TRIGGER command. You will also drop all DML triggers when you drop the table or view that they are bound to. You can also remove multiple triggers in the same DROP command if each of the triggers was created using the same ON clause.