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 whether to use a trigger for a specific set of 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 using a trigger. For example, if an inventory quantity should be updated after inserting a sales record, why not put this logic in the stored procedure instead?
• 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 large numbers 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 19-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 19-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 before and after the data modifications. These tables store data for both single and multirow updates. Triggers should be coded with both types of updates (single and multirow) in mind. For example, a DELETE statement may impact either a single row or multiple, say 50, rows—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
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 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
     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 Production.ProductInventory
     (ProductID,
     LocationID,
     Shelf,
     Bin,
     Quantity)
VALUES (316,
     6,
     'A',
     4,
     22);
  
-- Delete a row
DELETE 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 starts by creating a new table for tracking rows inserted to or deleted from the Production.ProductInventory table. The new table’s schema matches the original table but has added a new column named InsertOrUpdate to indicate whether the change was because of an INSERT or DELETE operation.

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 is created using CREATE TRIGGER. The schema and name of the new trigger are 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) is designated in the ON clause.

ON Production.ProductInventory
  

Two types of DML activity will be monitored: inserts and deletes.

AFTER INSERT, DELETE

The body of the trigger begins after the AS keyword.

AS
BEGIN

The SET NOCOUNT is 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 contains one INSERT statement of the form INSERT INTO ... SELECT where the SELECT statement is a UNION of two selects. The first SELECT in the UNION returns the rows from the INSERTED table, and the second SELECT in the union returns rows from the DELETED table.

First, set up the INSERT statement and specify the table into which the statement inserts rows as well as the columns that should be specified for each row that is inserted.

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

Next, select rows from the INSERTED table (this is a list of rows that are inserted into the ProductInventory table) and specify the columns that will 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 returns rows from the DELETED table and concatenates the results with a UNION ALL. The DELETED table contains a list of rows that are 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 is inserted into and then deleted from Production.ProductInventory.

INSERT Production.ProductInventory
     (ProductID,
     LocationID,
     Shelf,
     Bin,
     Quantity)
VALUES (316,
     6,
     'A',
     4,
     22);
  
-- Delete a row
DELETE 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;

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 and allow the application to insert into this view such that any new departments 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. They are allowed on both tables and views and are often used to handle data modifications to views that do not allow for 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 19-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.

-- 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 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 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 begins 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 is 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 prevents this view from being updateable. INSTEAD OF triggers allow data modifications against nonupdateable views.

A trigger is created to react to INSERTs against the view and insert the specified data into the approval table as long as the department name does 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 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 is tested against the view to see whether it is inserted in the approval table.

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

Query the view to show that the row is inserted and displays a “pending approval” status.

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

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 roll 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 undo any changes.

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 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 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
  

Now, attempt an insert of a row using ShelfA:

INSERT 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 the previous 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 Production.ProductInventory
WHERE ProductID = 853
     AND LocationID = 7 ;
-- Deleting a row with a non-zero quantity
DELETE 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 outer transaction is also rolled back. Even though the first row was a valid deletion because they were in the same calling transaction, neither row was deleted.

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 demonstrates 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.

Problem

You have a table, and you want to restrict updates to one column in the 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 has occurred against 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.

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 the 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 code is executed only against specific columns. Embedding the logic in conditional processing can help reduce the overhead each time the trigger fires.

Problem

You have a number of DML triggers defined in your database, and you want to list the triggers in the database 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
SELECT OBJECT_NAME(parent_id) Table_or_ViewNM,
     name TriggerNM,
         is_instead_of_trigger,
         is_disabled
FROM sys.triggers
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):

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)
SELECT o.name,
     m.definition
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 queries the sys.triggers catalog view to show all the DML triggers in the current database. There are DDL triggers in the sys.triggers catalog view as well. To prevent DDL from being displayed in the results, the query filters on parent_class_desc "OBJECT_0R_C0LUMN". DDL triggers have a different parent class, as we will discuss in Recipe 19-8.

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

Problem

You are testing index changes in a system and want to log any index 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 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 log the activity.

DDL triggers may be defined as database or server triggers. Database DDL triggers are stored as objects within the database that 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 19-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 AdventureWorks2012 database.

First, create an audit table to hold the results.

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

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

CREATE TRIGGER db_trg_INDEXChanges ON DATABASE
     FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
     SET NOCOUNT ON ;
         iNSERT 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 ChangeAttempt 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 24 for more information on XML in SQL Server).

EventData
----------------------------------------------------------------
<EVENT_INSTANCE>
     <EventType>CREATE_INDEX</EventType>
     <PostTime>2012-05-01T02:55:39.170</PostTime>
     <SPID>52</SPID>
     <ServerName>ANDYROB2012WIN7RTM</ServerName>
     <LoginName>NORTHAMERICAandyrob</LoginName>
     <UserName>dbo</UserName>
     <DatabaseName>AdventureWorks2012</DatabaseName>
     <SchemaName>dbo</SchemaName>
     <ObjectName>ni_ChangeAttempt_DBUser</ObjectName>
     <ObjectType>INDEX</ObjectType>
     <TargetObjectName>ChangeAttempt</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_ChangeAttempt_DBUser ON dbo.ChangeAttempt(DBUser) 
        </CommandText>
     </TSQLCommand>
</EVENT_INSTANCE>

How It Works

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

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

The DDL trigger is 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 is used in the trigger to suppress the number of row-affected messages from SQL Server (otherwise, every time you make an index modification, you’ll see a “1 row affected” message).

     SET NOCOUNT ON ;

A row is inserted to the audit table containing the event data and user who performed the statement that fired the event.

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

The EVENTDATA function returns server and data event information in XML format. The XML data returned from the EVENTDATA function includes 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).

Problem

You want to restrict the times at which certain users log into 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
     (
     LoginNM SYSNAME NOT NULL,
     AttemptDT 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
     (LoginNM, AttemptDT)
     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 LoginNM,
     AttemptDT
FROM ExampleAuditDB.dbo.RestrictedLogonAttempt
  

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

LoginNM	        AttemptDT
-----------	-----------------------
nightworker	2012-05-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 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 creates a test login, a new auditing database, and an auditing table to track attempts. The logon trigger is created in the master database. Stepping through the code, note that ALL SERVER is 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 is 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 designates 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 is an evaluation of the hour of the day. If the current time is between 7 a.m. and 6 p.m., two actions will be performed.

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

The first action is to roll back the logon attempt.

     ROLLBACK ;

The second action is to track the attempt to the audit table.

     INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
     (LoginNM, AttemptDT)
     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 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.RestrictedLogon-Attempt ’.

Problem

You want to list the server and database DDL triggers 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 trigger enabled/disabled status.

-- Show the DML triggers in the current database
SELECT name TriggerNM,
         is_disabled
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
ORDER BY OBJECT_NAME(parent_id),
     Name ;

This returns the following results:

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,
     s.type_desc S0L_or_CLR,
         is_disabled,
     e.type_desc 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 previous server-level trigger created earlier.

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.

SELECT t.name,
     m.Definition
FROM sys.triggers AS t
         iNNER JOIN sys.sql_modules m
     ON t.object_id = m.object_id
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.

SELECT t.name,
     m.definition
FROM sys.server_sql_modules m
         iNNER JOIN sys.server_triggers t
     ON m.object_id = t.object_id ;

How It Works

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

The third query returns 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 are joined to return a server-scoped trigger’s Transact-SQL definitions.

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 19-7. The login trigger should no longer restrict users from logging in but instead 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
     (LoginNM, AttemptDT)
     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 LoginNM,
     AttemptDT
FROM ExampleAuditDB.dbo.RestrictedLogonAttempt ;
  

The preceding select statement returns the following:

LoginNM            AttemptDT
---------------- -----------------------
nightworker 2012-05-01 012:20:19.577
nightworker 2012-05-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.

Problem

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

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. 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 | SERVER }

The syntax for enabling a trigger is as follows:

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

Table 19-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.

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.

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.

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 starts by creating a new trigger that prints a statement whenever a new row is inserted into the HumanResources.Department table.

After creating the trigger, the DISABLE TRIGGER command is used to keep it from firing (although the trigger’s definition still stays in the database).

DISABLE TRIGGER HumanResources.trg_Department
ON HumanResources.Department

An insert is performed that does not fire the trigger. The ENABLE TRIGGER command is then executed, and then another insert is attempted; this time, the INSERT fires the trigger.

Problem

Your trigger inserts data into another table with 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 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 uses the sp_configure system stored procedure to change the nested trigger behavior at the server level. To disable nesting altogether, sp_configure is executed for the nested trigger server option, followed by the parameter 0, which disables nesting.

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

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

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

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 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 AdventureWorks2012
SET RECURSIVE_TRIGGERS ON ;
  
-- View the db setting
SELECT is_recursive_triggers_on
FROM sys.databases
WHERE name = 'AdventureWorks2012' ;
  
-- Prevents recursion
ALTER DATABASE AdventureWorks2012
SET RECURSIVE_TRIGGERS OFF ;
  
-- View the db setting
SELECT is_recursive_triggers_on
FROM sys.databases
WHERE name = 'AdventureWorks2012' ;

This returns the following:

is_recursive_triggers_on 1

is_recursive_triggers_on 0

How It Works

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

ALTER DATABASE AdventureWorks2012
SET RECURSIVE_TRIGGERS ON ;

The option is then queried by using the sys.databases system catalog view that shows 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 = 'AdventureWorks2012' ;

The recipe then disables trigger recursion by setting the option OFF and confirms this by selecting from the sys.databases view.

Problem

Over time you have accumulated multiple triggers on the same table. You are concerned that the order that 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, conditions arise where 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 19-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.

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 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 starts by creating a single column test table, and three DML INSERT triggers are added to it. Using sp_settriggerorder, the first and last triggers to fire are defined.

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

An INSERT is then executed against the table, and the trigger messages are 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 your trigger 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 roll back transactions unnecessarily.

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 19-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 19-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 a DML and a DDL trigger.

-- Switch context back to the AdventureWorks2012 database
USE AdventureWorks2012 ;
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 were created using the same ON clause.