Using the EventData() Function with DDL triggers

Let's discuss how we store the events captured by DDL triggers and store data it in a table for reporting/auditing purposes?

In SQL Server 2005, we can get data regarding the event that initiated the DDL trigger by accessing the EventData() function. This function returns information about server or database events and is stored in a variable that uses the XML data type. What we need to do is capture the data returned by the EventData() function and store it in a database table for reporting purposes. Since the returned type is XML, we need to apply a bit of XQuery against the data generated by the function. The DDL_TABLE_EVENTS type describes what data can be captured for a CREATE, ALTER or DROP TABLE event.

We will create a table that will store the data returned by the EventData() function. We will only choose a few items from the schema generated by the ALTER_TABLE event; namely, the T-SQL Command, ExecuteTime, LoginName, and EventType which is included in every event.

Create the Audit log table

USE Northwind
GO
CREATE TABLE AuditLog
(
ExecuteTime DATETIME,
LoginName NVARCHAR(100),
EventType NVARCHAR(100),
Command NVARCHAR(2000)
)
GO

Now Create the DDL trigger

CREATE TRIGGER trgAlterTblChange
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @Data XML
SET @Data = EventData()
INSERT AuditLog (ExecuteTime, LoginName, EventType, Command)
VALUES
(GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),
@Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO

Let's understand the code.

Declare a variable named @Data of XML data type. Assign the value returned by the EventData() function to the variable.

In the INSERT statement, retrieve the element values in the @Data variable using XQuery and the value() method. We used value() method as it takes a second parameter, represented by the value 1 as it is based on the zero index, that is the name of one of the SQL Server built-in data types. The value is returned as an instance of that type. Also, the value() method gives you better results with the formatting of the XML data

Let's test it.

Add a new column "ShipAdd" to the Shippers table in the database

USE Northwind
GO

ALTER TABLE Shippers
ADD ShipAdd Varchar(100) NULL

Querying the AuditLog table will give you the data returned by the EventData() function, stored in tabular format as we have already extracted the element values inside the XML data from inside our trigger definition.

We can define DDL triggers as well as capture event details using the EventData() function on the database or server level. This approach is just to audit DDL activities and store the event details in a log table. We can have a central database that will store all the events done on both the database and server level. It is not efficient nor effective to audit every event due to the immense amount of audit record logging processes so make sure that you plan what events you need to audit.

Let's take a look at another example, as we already know that its not efficient to have stored procedure starting with 'sp_'. Let's use DDL triggers to stop people from creating procedures starting with 'sp_'

Let's create a trigger:

CREATE TRIGGER [ddltrg_CheckCreateProc] ON DATABASE
FOR CREATE_Procedure
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)

SET @xmlEventData = eventdata()

SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))

IF LEFT(@tableName, 3) = 'sp_'
BEGIN
RAISERROR ( 'You cannot create procedures starting with sp_',16,- 1 )
ROLLBACK
END

GO


Let's Test it:

create proc sp_testDDLTrigger
as
select getdate()


When we execute it, we get following error:

It throws the error as defined in the Trigger. This way we can force developers follow guidelines. SQL Server 2008 has introduced Policy based management, which help achieve same thing easily.