SQL Server supports three types of triggers. The first type is made up of DDL (data definition language) triggers, which fire in response to a DDL statement being executed, such as CREATE, ALTER, or DROP. You can use DDL triggers for auditing or for limiting DBA activity. The second type, logon triggers, fire when a session to the instance is established. You can use these triggers to stop a user from establishing a connection to an instance. The final type, DML triggers, fire as a result of a DML (data manipulation language) statement being executed, such as an INSERT, UPDATE, or DELETE statement. Developers use DML triggers to maintain complex logic and data integrity rules. This type of trigger is rarely used for administrative purposes, however, and is therefore beyond the scope of this book.

Triggers are code modules, similar to stored procedures. The difference is that triggers fire automatically when an event occurs in the instance, as opposed to being executed manually or via a script. This chapter discusses how DBAs can implement DDL and logon triggers to improve security and regulate administrative tasks.

DDL Triggers

The following sections first help you understand the concepts behind DDL triggers before moving on to discuss appropriate implementations.

Understanding DDL Triggers

You can create DDL triggers at either the instance level or the database level. When created at the instance level, they are said to be server scoped and can be used to respond to events such as new objects or logins being created. When triggers are scoped at the database level, they can respond to events such as tables being altered or dropped. You can also create triggers that respond to all database-level events at the server level so that they respond to events in all databases.

DDL triggers can provide you with a mechanism for auditing or limiting DBA activity, which can be very useful in environments where development or support teams need elevated permissions to instances or databases. You can use DLL triggers to limit the activity DBAs carry out, which can also prove useful if you have inexperienced or junior DBAs and want to ensure their actions are audited.

DDL triggers always execute after the event that caused them to fire, but within the context of the same transaction. This means that you can use a ROLLBACK statement within the body of the trigger to undo the actions the original statement performed. So, for example, if you implement a trigger at the instance level to restrict users from creating new databases, when a user runs a CREATE DTABASE statement, a ROLLBACK statement within the trigger undoes the action before it is committed.

While a trigger is executing, you have access to a system function called EVENTDATA(). This function returns a well-formed XML document, which conforms to the Event_Schema XSD schema. You can find full details of this schema at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd. The data included within this XML result set includes the SPID of the user who executed the original statement, when the event began, and the statement itself. This means that you can interrogate the XML result set using XQuery and insert the results into an audit table to ensure full reputability.

As well to respond to individual DDL events, you can also configure triggers to respond to DDL event groups. For example, the DDL_SERVER_LEVEL_EVENTS event group contains all events that occur only at the instance level. You can generate a list of the event groups available in SQL Server by running the query in Listing 24-1. This query returns each event name, along with the event group that contains it.

SELECT
        b.type_name GroupName
       , a.type_name EventName
FROM sys.trigger_event_types a
INNER JOIN sys.trigger_event_types b
       ON a.parent_type = b.type
ORDER BY b.type_name ;

You can modify this query so that it returns a list of events within a specific event group. You do this by having it return all type names from the instance of the table aliased as a and filter on the type_name column of the table aliased as b. For example, the query in Listing 24-2 returns all events captured by the DDL_TABLE_EVENTS event group, as shown in Figure 24-1.

SELECT a.type_name
FROM sys.trigger_event_types a
INNER JOIN sys.trigger_event_types b
       ON a.parent_type = b.type
WHERE b.type_name = ’DDL_TABLE_EVENTS’ ;

You can use the script in Listing 24-3 to view the complete event group hierarchy of an event. In this instance, we again focus on the CREATE, ALTER, and DROP TABLE events.

;WITH EventsHierarchy
AS
(
       SELECT
              type
              , type_name
              , CAST(type_name as NVARCHAR(4000)) AS type_hierarchy
              , parent_type
       FROM sys.trigger_event_types
       WHERE parent_type IS NULL

       UNION ALL

       SELECT
              TET.type
              , TET.type_name
              , CONCAT(TE.type_hierarchy,’  ’ , TET.type_name)
              , TET.parent_type
       FROM   sys.trigger_event_types TET
                    INNER JOIN EventsHierarchy TE
                            ON TET.parent_type = TE.type
)

-- Select results from CTE

SELECT
        type_name
        , type_hierarchy
FROM EventsHierarchy
WHERE type_name IN (’CREATE_TABLE’, ’ALTER_TABLE’, ’DROP_TABLE’)
ORDER BY type_name ; --type_hierarchy

The results of this query are displayed in Figure 24-2.

Implementing DDL Triggers

DDL triggers can respond to the following statements being issued:

• CREATE
• ALTER
• DROP
• GRANT
• DENY
• REVOKE
• UPDATE STATISTICS

To create DDL triggers, use the CREATE TRIGGER DDL statement. This statement has the basic structure shown in Listing 24-4.

CREATE TRIGGER <Name>
ON <Scope>
WITH <Options>
AFTER
AS
<code>

This statement accepts the arguments detailed in Table 24-1.

Implementing Server Scope DDL Triggers

Imagine a scenario in which development teams are creating test databases on an instance without seeking necessary approval. This kind of issue is surprisingly common and can make an environment very challenging to manage. We can resolve this issue by creating a server-scoped DDL trigger with a definition matching that of Listing 24-5. In this script, we create a trigger called PreventDatabaseCreation. In the body of the trigger, we declare a variable named @Message and populate this message with the login name of the user who ran the CREATE DATABASE statement, followed by a message informing the user that they are not following the correct process. We finally roll back the transaction. Because the trigger fires in the context of the same transaction as the original statement (even if the statement runs in autocommit mode as opposed to as an explicit transaction) the CREATE DATABASE statement is rolled back.

USE MASTER
GO

CREATE TRIGGER PreventDatabaseCreation
ON ALL SERVER
AFTER CREATE_DATABASE
AS
BEGIN
        DECLARE @Message NVARCHAR(256) ;
        SET @Message =
            (SELECT SUSER_SNAME()
              + ’, you must follow due process in order to create Databases in this environment. Pleae contact the DBA Team’) ;
        PRINT @Message ;
        ROLLBACK ;
END

If we use the statement in Listing 24-6 to attempt to create the Chapter24 database, we see the result illustrated in Figure 24-3. This result indicates that the transaction has been rolled back and it also displays our custom message to the user.

CREATE DATABASE Chapter24 ;

After implementing such a trigger, you obviously need to change your process for creating new databases so that your team still has the ability to create databases in a controlled fashion. The easiest way to do this is to ensure that all CREATE DATABASE statements are preceded by a statement to disable the trigger and succeeded by a statement to reenable the trigger. You can achieve this by using the DISABLE TRIGGER and ENABLE TRIGGER statements, as demonstrated in Listing 24-7.

--Disable the trigger

DISABLE TRIGGER PreventDatabaseCreation ON ALL SERVER ;

--Create the database

CREATE DATABASE Chapter24 ;

--Enable the trigger

ENABLE TRIGGER PreventDatabaseCreation ON ALL SERVER ;

To enable or disable all triggers at a particular scope, you can use the ALL keyword. For example, the command in Listing 24-8 disables all triggers at the server scope before reenabling them.

--Disable all triggers at the server scope

DISABLE TRIGGER ALL ON ALL SERVER ;

--Enable all triggers at the server scope

ENABLE TRIGGER ALL ON ALL SERVER ;

Implementing Database Scope DDL Triggers

Imagine a scenario in which you require an audit on any changes to programmable object definitions within the Chapter24 database. You should not prevent the changes from occurring, but you want all changes logged to an audit table. The first step in implementing a solution for this scenario is to create an audit table in the Chapter24 database. We can do this using the script in Listing 24-9.

USE Chapter24
GO

CREATE TABLE dbo.Audit
(
ID                    INT                 IDENTITY        PRIMARY KEY,
PostTime              DATETIME2           NOT NULL,
LoginName             NVARCHAR(128)       NOT NULL,
DatabaseName          NVARCHAR(128)       NOT NULL,
EventType             NVARCHAR(128)       NOT NULL,
SchemaName            NVARCHAR(128)       NULL,
ObjectName            NVARCHAR(128)       NOT NULL,
CommandText           NVARCHAR(MAX)       NOT NULL
) ;

Next, we need to create the trigger itself. Listing 24-10 demonstrates how to create this trigger. We name the trigger DDLAudit and define it to fire after any programmable objects, or other objects that contain code, are created, altered, or dropped. The WITH EXECUTE AS SELF clause means that the trigger executes under the security context of the user who created the trigger. This is beneficial, because it means we do not have to grant permissions to the Audit table to all users who issue DDL statements. This is possible because both objects are owned by the same user, within the same database.

In the body of the trigger, we use the XQuery Value method, which returns scalar values from XML, to pull data from the results of the EVENTDATA() function. We then use this data to populate the Audit table.

USE Chapter24
GO

CREATE TRIGGER DDLAudit
ON DATABASE
WITH EXECUTE AS SELF
AFTER CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_TRIGGER, CREATE_ASSEMBLY, CREATE_TYPE, CREATE_RULE, CREATE_DEFAULT, CREATE_PLAN_GUIDE, CREATE_SEQUENCE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_TRIGGER, ALTER_ASSEMBLY, ALTER_PLAN_GUIDE, ALTER_SEQUENCE, DROP_PROCEDURE, DROP_FUNCTION, DROP_TRIGGER, DROP_ASSEMBLY, DROP_TYPE, DROP_RULE, DROP_DEFAULT, DROP_PLAN_GUIDE, DROP_SEQUENCE
AS
BEGIN
        INSERT INTO dbo.Audit
        SELECT EVENTDATA().value(’(/EVENT_INSTANCE/PostTime)[1]’,’datetime2’),
               EVENTDATA().value(’(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(128)’),
               EVENTDATA().value(’(/EVENT_INSTANCE/DatabaseName)[1]’,’nvarchar(128)’),
               EVENTDATA().value(’(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(128)’),
               EVENTDATA().value(’(/EVENT_INSTANCE/SchemaName)[1]’,’nvarchar(128)’),
               EVENTDATA().value(’(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(128)’),
               EVENTDATA().value(’(/EVENT_INSTANCE/TSQLCommand/CommandText)               [1]’,’nvarchar(max)’) ;
END

In order to test our trigger, we create a simple stored procedure called TestTrigger in the Chapter24 database. We can do this using the script in Listing 24-11.

USE Chapter24
GO

CREATE PROCEDURE TestTrigger
AS
BEGIN
        SELECT 1 ;
END

If you run a SELECT * statement against our dbo.Audit table, you see results similar to those in Figure 24-4.

Trigger Metadata

You can find metadata regarding server-scoped DDL triggers in the sys.server_triggers catalog view. This view returns the columns detailed in Table 24-2.

You can use the sys.triggers catalog view to find details of triggers defined within the scope of a database. Table 24-3 describes the columns that this catalog view returns.

You can use the sys.triggers and sys.server_triggers catalog views together to produce a query that returns a list of all user-defined DDL triggers within the instance, along with their scope. The script in Listing 24-12 achieves this by inserting the names of the server-scoped triggers into a global temporary table before using the XML data() function to insert the triggers for each database into the global temporary table. You can then access the temporary table with a simple SELECT statement. Finally, this script cleans up the temporary table, which makes the script rerunnable.

CREATE TABLE #Triggers
(name        NVARCHAR(128),
scope        NVARCHAR(128)) ;

INSERT INTO #Triggers
SELECT
        name
        ,’Server’
FROM sys.server_triggers ;

DECLARE @SQL        NVARCHAR(MAX) ;

SET @SQL = (SELECT ’INSERT INTO #Triggers SELECT name, ’’’
                                                + d.name
                                                + ’’’ FROM ’
                                                + d.name
                                                + ’.sys.triggers WHERE parent_class = 0 AND is_ms_shipped = 0 ; ’ AS [data()]
                        FROM sys.databases d
                        FOR XML PATH (’’)) ;

EXEC (@SQL) ;

SELECT *
FROM #Triggers ;

DROP TABLE #Triggers ;

Logon Triggers

Logon triggers are very similar to DDL triggers, except that instead of firing in response to DDL events, they fire in response to a LOGON event occurring on the instance. This is the same event as the AUDIT_LOGON trace event. The advantage that a logon trigger offers over tracing and auditing is its ability to actually stop a user from establishing a connection. This is because the trigger fires synchronously with the event, as opposed to asynchronously.

Logon triggers prove most useful when you need to limit connections to the instance. For example, imagine that you have a reporting application that has a complex ETL process that runs between 8PM and 11PM every evening; however, you have an issue with staff accessing the application between these times and seeing an inconsistent version of the data. In this scenario, you may wish to use a logon trigger to stop any users, other than the service account that runs the ETL processes, from accessing the instance.

The script in Listing 24-13 creates a logon trigger, called PreventingLoginDuringETL, to avoid any users establishing sessions between 8PM and 11PM, with the exception of the service account that runs the ETL loads. The script uses the time portion of the GETDATE() system function to establish the current server time and the ORIGINAL_NAME() system function to determine the login.

CREATE TRIGGER PreventLoginDuringETL
ON ALL SERVER
FOR LOGON
AS
BEGIN
        IF (CAST(GETDATE() AS TIME) >= CAST(’20:00:00’ AS TIME)
                AND CAST(GETDATE() AS TIME) <= CAST(’23:00:00’ AS TIME)
                AND UPPER(ORIGINAL_LOGIN()) <> ’ETLSVCACC’)
        ROLLBACK ;
END

Another example of when a logon trigger is useful for limiting connections is a scenario in which you are hosting a data-tier application that has a limited number of user licenses associated with it, and you need to ensure that the number of concurrent users accessing the database does not exceed this limit. For example, if our instance is hosting a data-tier application with a license for a maximum of 100 concurrent users, then we can use the script in Listing 24-14 to create a trigger called EnforceLicensing. This trigger performs a count of the concurrent logins to the instance and if this exceeds 100, then subsequent connections fail to establish a connection until the number of concurrent users drops below the threshold.

CREATE TRIGGER EnforceLicensing
ON ALL SERVER
FOR LOGON
AS
BEGIN
        IF (SELECT COUNT(DISTINCT login_name)
                FROM sys.dm_exec_sessions
                WHERE is_user_process = 1) > 100
        ROLLBACK ;
END

If a connection is rolled back due to trigger execution, then the user receives a message similar to the one illustrated in Figure 24-5.

Controlling Trigger Order

It is possible to create multiple DDL (and logon) triggers that respond to the same event. If you create multiple triggers that are triggered by the same transaction, then you have some, but not complete, control over the order in which they fire.

You can exercise this control by using the sp_settriggerorder system stored procedure. This procedure allows you to control the first and the last trigger that are fired. If you have more than three triggers that respond to the same event, then you have no way to guarantee the order of the triggers that are not marked as the first trigger or the last trigger. The sp_settriggerorder procedure accepts the parameters detailed in Table 24-4.

It is important to note that it is possible to mark triggers as First and Last for both the server scope and the database scope. The limitation here, however, is that the server trigger marked as First will fire before the database trigger marked as First. This is because all server-level triggers fire before any database-level triggers fire.

The script in Listing 24-15 configures our EnforceLicensing logon trigger to fire before our PreventLoginDuringETL trigger. If we subsequently create any further logon triggers on the instance, then they fire after the EnforceLicensing trigger but before the PreventLoginTrigger trigger.

USE Master
GO

EXEC sp_settriggerorder @triggername=’EnforceLicensing’, @order=’First’, @stmttype=’LOGON’, @namespace=’SERVER’ ;

EXEC sp_settriggerorder @triggername=’PreventLogindURINGetl’, @order=’Last’, @stmttype=’LOGON’, @namespace=’SERVER’ ;

Summary

You can use DDL triggers to restrict changes to objects or audit user activity. They always fire after the event that caused them to fire, but within the context of the same transaction. This means that you can always roll back the action that caused the trigger to fire if you need to. You can scope DDL triggers at the instance level or at the database level, and you can configure them to respond to specific events or groups of events.

Within the context of a DDL trigger, you have access to the EVENTDATA() function, which contains information about the action that caused the trigger to fire. This information includes the time that the event occurred, the user that executed the event, and the statement itself. This data is provided in XML format, so it can be interrogated using XML and can be used for auditing purposes.

Logon triggers are always scoped at the instance level and fire when a connection is made to the instance. Logon triggers fire synchronously with the logon, and thus you can use them to prevent a connection from being established. This can be useful in circumstances in which you want to impose a limit on the number of concurrent users.

You can find metadata regarding server-scoped triggers in the sys.server_triggers catalog view and metadata regarding database-scoped triggers in the sys.triggers catalog view from within the context of the relevant database. To a limited extent, you can control the order in which triggers fire by using the sp_settrigger order system stored procedure. This procedure allows you to mark a trigger as either first or last. When more than three triggers fire in response to the same event, it is not possible to control the order of the other triggers.