Database-level DDL triggers allow you to react to database-level events, such as creating, altering, or dropping objects. Here’s the syntax for the header of a database-level DDL trigger:

CREATE TRIGGER <trigger name>
  ON DATABASE
  FOR <one or more statements or statement groups>

You must be connected to the target database when creating the trigger.

In my examples, I will use a database called testdb, which you create by running the following code:

USE master;
GO
IF DB_ID('testdb') IS NOT NULL
  DROP DATABASE testdb;
GO
CREATE DATABASE testdb;
GO
USE testdb;

Suppose that you want to enforce a company policy in the testdb database that says that when you create a table you must define a primary key. You create the trigger shown in Example 8-10 to achieve this task.

CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS

DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
  @msg AS NVARCHAR(500);

SET @eventdata = eventdata();
SET @objectname =
  + QUOTENAME(CAST(@eventdata.query('data(//SchemaName)') AS sysname))
  + N'.' +
  QUOTENAME(CAST(@eventdata.query('data(//ObjectName)') AS sysname));

IF COALESCE(
     OBJECTPROPERTY(OBJECT_ID(@objectname), 'TableHasPrimaryKey'),
     0) = 0
BEGIN
  SET @msg = N'Table ' + @objectname + ' does not contain a primary key.'
    + CHAR(10) + N'Table creation rolled back.';
  RAISERROR(@msg, 16, 1);
  ROLLBACK;
  RETURN;
END
GO

The trigger is naturally created for CREATE_TABLE statements. The trigger’s code first assigns the return XML value from the eventdata function to a local variable called @eventdata. The code then extracts the event attributes SchemaName and ObjectName, using XQuery expressions, and it constructs a schema-qualified table name in the @objectname variable. Finally, the code uses the OBJECTPROPERTY function to check whether the table contains a primary key. If it doesn’t, the code generates an error message and rolls back the table creation.

To test the trigger, first try to create a table without a primary key:

CREATE TABLE dbo.T(col1 INT NOT NULL);

You will get the following error:

Server: Msg 50000, Level 16, State 1, Procedure trg_create_table_with_pk, Line 19
Table [dbo].[T] does not contain a primary key.
Table creation rolled back.
Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Then try to create a table with a primary key, and your code will run successfully:

CREATE TABLE dbo.T(col1 INT NOT NULL PRIMARY KEY);

As I mentioned earlier, DDL triggers can be used to audit DDL events–finally! The trigger’s implementation is straightforward: use XQuery expressions to query the individual event attributes that you want to audit and load them to the audit table. That’s all there is to it. As an example, run the code in Example 8-11 to create the AuditDDLEvents table and the trg_audit_ddl_events trigger.

IF OBJECT_ID('dbo.AuditDDLEvents') IS NOT NULL
  DROP TABLE dbo.AuditDDLEvents;
GO

CREATE TABLE dbo.AuditDDLEvents
(
  lsn              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        sysname  NOT NULL,
  loginname        sysname  NOT NULL,
  schemaname       sysname  NOT NULL,
  objectname       sysname  NOT NULL,
  targetobjectname sysname  NOT NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(lsn)
);
GO

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @eventdata AS XML;
SET @eventdata = eventdata();

INSERT INTO dbo.AuditDDLEvents(
  posttime, eventtype, loginname, schemaname,
  objectname, targetobjectname, eventdata)
  VALUES(
    CAST(@eventdata.query('data(//PostTime)')         AS VARCHAR(23)),
    CAST(@eventdata.query('data(//EventType)')        AS sysname),
    CAST(@eventdata.query('data(//LoginName)')        AS sysname),
    CAST(@eventdata.query('data(//SchemaName)')       AS sysname),
    CAST(@eventdata.query('data(//ObjectName)')       AS sysname),
    CAST(@eventdata.query('data(//TargetObjectName)') AS sysname),
    @eventdata);
GO

The trigger is so simple that all it has is an assignment of the eventdata value to a local XML variable and a single INSERT statement that loads the event attributes into the audit table. Note that it also loads the full XML value in case you want to query attributes that were not extracted individually.

To test the trigger, issue the following DDL events:

CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
ALTER TABLE dbo.T1 ADD col2 INT NULL;
ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL;
CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2);

Then query the audit table, and you will get the output shown in abbreviated form in Table 8-10:

SELECT * FROM dbo.AuditDDLEvents;

Of course, you will get different values in posttime and loginname attributes.

Suppose that you come to work next morning and realize that schema changes took place against a table called T1. You realize this after users keep calling you complaining that the application breaks. You ask around to see whether someone applied a schema change to T1 in the last 24 hours, but naturally everyone is silent, choosing to exercise their Fifth Amendment rights. Fortunately (for you), you can query the audit table. You can even use an XQuery expression to extract event attributes that were not recorded individually. Here’s the query that you would use, producing the output shown in Table 8-11:

SELECT posttime, eventtype, loginname,
  CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
  AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
  AND posttime > GETDATE() - 1
ORDER BY posttime;

CREATE TABLE dbo.T1(col1 INT
NOT NULL PRIMARY KEY);


ALTER TABLE dbo.T1 ADD col2
INT NULL;


ALTER TABLE dbo.T1 ALTER
COLUMN col2 INT NOT
NULL;


CREATE NONCLUSTERED INDEX
idx1 ON dbo.T1(col2);



Now you know exactly which schema changes took place and who submitted them–in some cases, you see that it was you who applied the change and that you suffered from a slight case of amnesia.

Caution

XML quoting of certain characters creates a security problem. Hackers can inject XML elements through object names or even character strings in the statement that fires the trigger. For example, if you create a table called [>], the XML value returned by the eventdata() function will have the object name: >, and the command text CREATE TABLE [>](c int);.

Note that SQL Server Management Studio (SSMS) provides solutions to some of the needs that I discussed in this section. For example, one of the reports on the SSMS summary page is "Schema change history," so a DDL trigger is not the only option for this.

When you’re done, run the following code for cleanup:

USE master;
GO
IF DB_ID('testdb') IS NOT NULL
  DROP DATABASE testdb;

Server-level DDL triggers can be defined for server-level events. Examples of such events are creation of databases, changes to logins, and so on. You develop server-level triggers in a similar manner to database-level ones. In the trigger’s header, you specify the following: ON ALL SERVER instead of ON DATABASE.

As an example, suppose that you want to audit CREATE, ALTER, and DROP statements for logins. Run the code in Example 8-12 to create the AuditDDLLogins table and the trg_audit_ddl_logins trigger.

USE master;
GO
IF OBJECT_ID('dbo.AuditDDLLogins') IS NOT NULL
  DROP TABLE dbo.AuditDDLLogins;
GO

CREATE TABLE dbo.AuditDDLLogins
(
  lsn              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        sysname  NOT NULL,
  loginname        sysname  NOT NULL,
  objectname       sysname  NOT NULL,
  logintype        sysname  NOT NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLLogins PRIMARY KEY(lsn)
);
GO

CREATE TRIGGER trg_audit_ddl_logins ON ALL SERVER
  FOR DDL_LOGIN_EVENTS
AS
DECLARE @eventdata AS XML;
SET @eventdata = eventdata();

INSERT INTO master.dbo.AuditDDLLogins(
  posttime, eventtype, loginname,
  objectname, logintype, eventdata)
  VALUES(
    CAST(@eventdata.query('data(//PostTime)')         AS VARCHAR(23)),
    CAST(@eventdata.query('data(//EventType)')        AS sysname),
    CAST(@eventdata.query('data(//LoginName)')        AS sysname),
    CAST(@eventdata.query('data(//ObjectName)')       AS sysname),
    CAST(@eventdata.query('data(//LoginType)')        AS sysname),
    @eventdata);
GO

This audit trigger’s code is almost identical to the audit trigger you created earlier; it just has different event attributes, which are relevant to login-related DDL events.

Caution

Note that this trigger suffers from the same security problem as before with characters that XML must quote. Try, for example, to create a login called [l<gin], and you will find that the login name that appears in the audit table is l&lt;gin.

To test the trigger, issue the following login-related statements for creating, altering, and dropping a login:

CREATE LOGIN login1 WITH PASSWORD = '123';
ALTER LOGIN login1 WITH PASSWORD = 'xyz';
DROP LOGIN login1;

Next query the audit table, and you will get the output shown in abbreviated form in Table 8-12:

SELECT * FROM master.dbo.AuditDDLLogins;

When you’re done, run the following code for cleanup:

DROP TRIGGER trg_audit_ddl_logins ON ALL SERVER;
GO
IF OBJECT_ID('dbo.AuditDDLLogins') IS NOT NULL
  DROP TABLE dbo.AuditDDLLogins;