Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 369

The following code creates the dbo.AuditDDLEvents table, which holds the audit information.

IF OBJECT_ID('dbo.AuditDDLEvents', 'U') IS NOT NULL 

  DROP TABLE dbo.AuditDDLEvents; 

 

CREATE TABLE dbo.AuditDDLEvents 

( 

  audit_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  NULL, 

  eventdata        XML      NOT NULL, 

  CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(audit_lsn) 

);

Notice that the table has a column called eventdata that has an XML data type. In addition to the 

individual attributes that the trigger extracts from the event information and stores in individual at-

tributes, it also stores the full event information in the eventdata column. 

Run the following code to create the trg_audit_ddl_events audit trigger on the database by using 

the event group DDL_DATABASE_LEVEL_EVENTS , which represents all DDL events at the database 

level.

CREATE TRIGGER trg_audit_ddl_events 

  ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS 

AS 

SET NOCOUNT ON; 

 

DECLARE @eventdata AS XML = eventdata(); 

 

INSERT INTO dbo.AuditDDLEvents( 

  posttime, eventtype, loginname, schemaname,  

  objectname, targetobjectname, eventdata) 

  VALUES( 

    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',         'VARCHAR(23)'), 

    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',        'sysname'), 

    @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',        'sysname'), 

    @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]',       'sysname'), 

    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',       'sysname'), 

    @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'), 

    @eventdata); 

GO

www.it-ebooks.info

---

370  

Microsoft SQL Server 2012 T-SQL Fundamentals

The trigger’s code first stores the event information obtained from the EVENTDATA function in the 

@eventdata variable. The code then inserts a row into the audit table with the attributes extracted by 

using XQuery expressions by the .value method from the event information, plus the XML value with 

the full event information. 

To test the trigger, run the following code, which contains a few DDL statements.

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

Next, run the following code to query the audit table.

SELECT * FROM dbo.AuditDDLEvents;

You get the following output (split here into two sections for display purposes), but with values in 

the posttime and loginname attributes that reflect the post time and logon name in your environment.

audit_lsn posttime                  eventtype      loginname 

--------- ------------------------- -------------- ---------------- 

1         2012-02-12 09:06:18.293   CREATE_TABLE   K2\Gandalf 

2         2012-02-12 09:06:18.413   ALTER_TABLE    K2\Gandalf 

3         2012-02-12 09:06:18.423   ALTER_TABLE    K2\Gandalf 

4         2012-02-12 09:06:18.423   CREATE_INDEX   K2\Gandalf 

 

audit_lsn   schemaname    objectname    targetobjectname  eventdata 

----------- ------------- ------------- ----------------- ------------------- 

1           dbo           T1            NULL              ... 

2           dbo           T1            NULL              ... 

3           dbo           T1            NULL              ... 

4           dbo           idx1          T1                ...

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

DROP TRIGGER trg_audit_ddl_events ON DATABASE; 

DROP TABLE dbo.AuditDDLEvents;