From Beginning SQL Server 2005 for Developers: From Novice to Professional, Berkeley, Apress, January 2006.

Part 1  |  Part 2  |  Part 3

DDL Triggers

Checking whether an action has happened on an object within SQL Server either on a database or within the server is not code that you will write every day. As more and more audit requirements are enforced on companies to ensure that their data is safe and has not been amended, auditors are now also turning their attention to areas that may cause that data to be altered. A DDL trigger is like a data trigger, as it can execute on the creation, deletion, or modification of rows within system tables rather than on user tables. So how does this help you?

I am sure we can all recall specific stories involving major institutions having a program running that removed funds or stock. My favorite is one in which a developer wrote a program that calculated interest on clients’ accounts. Obviously, there needed to be roundings, so the bank always rounded down to the nearest cent. However, all the “down roundings” added up each month to a fairly substantial amount of money. Of course, auditors saw that the data updates were correct, as the amount on the transaction table matched the amount in the client’s account. The interest calculation stored procedure also passed QA at the time. However, once it was live, the developer altered the stored procedure so that all the down roundings were added up in a local variable, and at the end of the process, the amount was added to a “hidden” account. It was a simple stored procedure that never went wrong, and of course it was encrypted so nobody could see what the developer had done. If the stored procedure needed an update,it was the “old” correct code that went live, and the developer simply waited until the time was right and reapplied his code. Auditors could not figure out why at a global level thousands of dollars could not be accounted for over time. Of course, eventually they did, but if they had a DDL trigger so that whenever a stored procedure was released they received an e-mail or some other notification, they could have immediately seen two releases of the stored procedure and been asking “Why?” within minutes. Our example will demonstrate this in action.

First of all, let’s look at database scoped events.

DDL_DATABASE_LEVEL_EVENTS

This section presents a listing of all the events that can force a DDL trigger to execute. Similar to DML triggers that can execute on one or more actions, a DDL trigger can also be linked to one or more actions. However, a DDL trigger is not linked to a specific table or type of action. Therefore, one trigger could execute on any number of unrelated transactions. For example, the same trigger could fire on a stored procedure being created, a user login being dropped, and a table being altered. I doubt if you will create many if any triggers like this, but it is possible.

There are two ways that you can create a trap for events that fire. It is possible to either trap these events individually (or as a comma-separated list) or as a catchall. You will see how to do this once we have looked at what events are available.

Database-scoped Events

The following table lists all the DDL database actions that can be trapped. This is quite a comprehensive list and covers every database event there is. Many of the actions you will recognize from previous chapters, although the commands have spaces between words rather than underscores.

CREATE_TABLE                     ALTER_TABLE                     DROP_TABLE
CREATE_VIEW                      ALTER_VIEW                      DROP_VIEW
CREATE_SYNONYM                   DROP_SYNONYM                    CREATE_FUNCTION
ALTER_FUNCTION                   DROP_FUNCTION                   CREATE_PROCEDURE
ALTER_PROCEDURE                  DROP_PROCEDURE                  CREATE_TRIGGER
ALTER_TRIGGER                    DROP_TRIGGER                    CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION          CREATE_INDEX                    ALTER_INDEX
DROP_INDEX                       CREATE_STATISTICS               UPDATE_STATISTICS
DROP STATISTICS                  CREATE_ASSEMBLY                 ALTER_ASSEMBLY
DROP_ASSEMBLY                    CREATE_TYPE                     DROP_TYPE
CREATE_USER                      ALTER_USER                      DROP_USER
CREATE_ROLE                      ALTER_ROLE                      DROP_ROLE
CREATE_APPLICATION_ROLE          ALTER_APPLICATION_ROLE          DROP_APPLICATION_ROLE
CREATE_SCHEMA                    ALTER_SCHEMA                    DROP_SCHEMA
CREATE_MESSAGE_TYPE              ALTER_MESSAGE_TYPE              DROP_MESSAGE_TYPE
CREATE_CONTRACT                  ALTER_CONTRACT                  DROP_CONTRACT
CREATE_QUEUE                     ALTER_QUEUE                     DROP_QUEUE
CREATE_SERVICE                   ALTER_SERVICE                   DROP_SERVICE
CREATE_ROUTE                     ALTER_ROUTE                     DROP_ROUTE
CREATE_REMOTE_SERVICE_BINDING    ALTER_REMOTE_SERVICE_BINDING    DROP_REMOTE_SERVICE_BINDING
GRANT_DATABASE                   DENY_DATABASE                   REVOKE_DATABASE
CREATE_SECEXPR                   DROP_SECEXPR                    CREATE_XML_SCHEMA
ALTER_XML_SCHEMA                 DROP_XML_SCHEMA                 CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION         DROP_PARTITION_FUNCTION         CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME     DROP_PARTITION_SCHEME

DDL Statements with Server Scope

Database-level events are not the only events that can be trapped within a trigger; server events can also be caught.

Following are the DDL statements that have the scope of the whole server. Many of these you may not come across for a while, if at all, so we will concentrate on database-scoped events.

CREATE_LOGIN             ALTER_LOGIN             DROP_LOGIN
CREATE_HTTP_ENDPOINT     DROP_HTTP_ENDPOINT      GRANT_SERVER_ACCESS
DENY_SERVER_ACCESS       REVOKE_SERVER_ACCESS    CREATE_CERT
ALTER_CERT               DROP_CERT

A DDL trigger can also accept every event that occurs within the database and, within the T-SQL code, decide what to do with each event, from ignoring upwards. However, catching every event results in an overhead on every action.

Note: It is not possible to have a trigger that fires on both server and database events; it’s one or the other.

The syntax for a DDL trigger is very similar to that for a DML trigger:

  CREATE TRIGGER trigger_name
  ON {ALL SERVER|DATABASE}
  [WITH ENCRYPTION]
  {
  {{FOR |AFTER } {event_type,…}
  AS
  sql_statements}}

The main options that are different are as follows:

• ALL SERVER|DATABASE: The trigger will fire either for the server or the database you are attached to when creating the trigger.
• Event_type: This is a comma-separated list from either the database or server list of DDL actions that can be trapped.

Note: You can also catch events that can be grouped together. For example, all table and view events can be defined with a group, or this group can be refined down to just table events or view events. The only grouping we will look at is how to catch every database-level event.

Dropping a DDL trigger

Removing a DDL trigger from the system is not like removing other objects where you simply say DROP object_type object_name. With a DDL trigger, you have to suffix this with the scope of the trigger.

DROP TRIGGER trigger_name ON {DATABASE|ALL SERVER}

EVENTDATA()

As an event fires, although there are no INSERTED and DELETED tables to inspect what has changed, you can use a function called EVENTDATA(). This function returns an XML data type containing information about the event that fired the trigger. The basic syntax of the XML data is as follows, although the contents of the function will be altered depending on what event fired.

     <SQLInstance>
         <PostTime>date-time</PostTime>
         <SPID>spid</SPID>
         <ComputerName>name</ComputerName>
     </SQLInstance>

I won’t detail what each event will return in XML format, otherwise we will be here for many pages. However, in one of the examples that follow we will create a trigger that will fire on every database event, trap the event data, and display the details.

Database-level events have the following base syntax, different from the previously shown base syntax:

     <SQLInstance>
         <PostTime>date-time</PostTime>
         <SPID>spid</SPID>
         <ComputerName>name</ComputerName>
         <DatabaseName>name</DatabaseName>
         <UserName>name</UserName>
         <LoginName>name</LoginName>
     </SQLInstance>

The XML elements can be described as follows:

• PostTime: The date and time of the event firing
• SPID: The SQL Server process ID that was assigned to the code that caused the trigger to fire
• ComputerName: The name of the computer that caused the event to fire
• DatabaseName: The name of the database that caused the event to fire
• UserName: The name of the user who caused the event to fire
• LoginName: The login name of the user who caused the event to fire

It’s time to see a DDL trigger in action.

Try It Out: DDL Trigger

1. This first example will create a trigger that will execute when a stored procedure is created, altered, or dropped. When it finds this action, it will check the time of day, and if the time is during the working day, then the action will be disallowed and be rolled back. On top of this, we will raise an error listing the stored procedure. This will allow you to see how to retrieve information from the EVENTDATA() function. The final action is to roll back the changes if an action is happening during the working day.

   CREATE TRIGGER trgSprocs
   ON DATABASE
   FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
   AS
   IF DATEPART(hh,GETDATE()) > 9 AND DATEPART(hh,GETDATE()) < 17
   BEGIN
       DECLARE @Message nvarchar(max)
       SELECT @Message =
         'Completing work during core hours. Trying to release - '
         + EVENTDATA().value
           ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
      RAISERROR (@Message, 16, 1)
      ROLLBACK
   END

2. We can now test the trigger. Depending on what time of day you run the code, the following will either succeed or fail.

   CREATE PROCEDURE Test1
   AS
   SELECT 'Hello all'

3. Try running the preceding code between 9 a.m. and 5 p.m. so that it is possible to see the creation fail. Running the code in the afternoon provided me with the following error:

   Msg 50000, Level 16, State 1, Procedure trgSprocs, Line 11
   Completing work during core hours.
   Trying to release - CREATE PROCEDURE Test1
   AS
   SELECT 'Hello all'
   Msg 3609, Level 16, State 2, Procedure Test1, Line 3
   The transaction ended in the trigger. The batch has been aborted.

4. It is necessary to drop the preceding trigger so we can move on, unless of course you are now outside of the prohibited hours and you wish the trigger to remain:

   DROP TRIGGER trgSprocs ON DATABASE

5. We can create our second DDL trigger. This time we will not look for any specific event but wish this trigger to execute on any action that occurs at the database. This will allow us to see the XML data generated on any event we want to.

   CREATE TRIGGER trgDBDump
   ON DATABASE
   FOR DDL_DATABASE_LEVEL_EVENTS
   AS
       SELECT EVENTDATA()

6. This trigger can be tested by successfully creating the stored procedure we couldn’t in our first example.

   CREATE PROCEDURE Test1
   AS
   SELECT 'Hello all'

7. Check the results window. You should see results that you have not seen before. What is returned is XML data, and the results window displays the data as shown in Figure 13-7.
   
   Figure 13-7. Event data XML
8. If you click the row, a new Query Editor pane opens after a few moments, and the XML data is transposed into an XML document layout. Each of the nodes can be inspected just like the CommandText node was earlier.

   <EVENT_INSTANCE>
     <EventType>CREATE_PROCEDURE</EventType>
     <PostTime>2005-09-04T14:24:14.593</PostTime>
     <SPID>61</SPID>
     <ServerName>XP-PRO</ServerName>
     <LoginName>XP-PRO\rdewson</LoginName>
     <UserName>dbo</UserName>
     <DatabaseName>ApressFinancial</DatabaseName>
     <SchemaName>dbo</SchemaName>
     <ObjectName>Test1</ObjectName>
     <ObjectType>PROCEDURE</ObjectType>
     <TSQLCommand>
       <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
   QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
       <CommandText>CREATE PROCEDURE Test1
   AS
   SELECT 'Hello all'
   </CommandText>
     </TSQLCommand>
   </EVENT_INSTANCE>

Summary

DML triggers should be seen as specialized and specific stored procedures set up to help your system with maintaining data integrity, cascading updates throughout a system, or enforcing business rules. If you take out the fact that there are two system tables, INSERTED and DELETED, and that you can check what columns have been modified, then the whole essence of a trigger is that it is a stored procedure that runs automatically when a set data-modification condition arises on a specific table.

DDL triggers will be built mainly for security or reporting of system changes to compliance departments and the like. With the EventData() XML information available to a trigger, a great deal of useful information can be inspected and used further.

Coding a trigger is just like coding a stored procedure with the full control of flow, error handling, and processing that is available to you within a stored procedure object.

The aim of this chapter was to demonstrate how a trigger is fired, and how to use the information that is available to you within the system to update subsequent tables or to stop processing and rollback the changes.

The DML triggers built within this chapter have demonstrated how to use the virtual tables, as well as how to determine whether a column has been modified. The DDL triggers built have demonstrated how you can trap events and determine what has been changed either within a database or a server.

--

Robin Dewson has been hooked on programming ever since he bought his first computer, a Sinclair ZX80, in 1980. He has been working with SQL Server since version 6.5 and Visual Basic since version 5. Robin is a consultant mainly in the city of London, where he has been for nearly eight years. He also has been developing a rugby-related website as well as maintaining his own site at www.fat-belly.com.