Now let’s start implementing some DDL triggers by using T-SQL. Open SQL Server Management Studio and connect to the Northwind database –

When you execute the drop table command, you will encounter the user defined message shown above.

Let’s drop the trigger for the time being –

DROP TRIGGER NoDropTables ON DATABASE

Now let’s write a trigger which will disallow all the DDL operations, as shown below –

CREATE TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
PRINT 'You are not allowed to Perform DDL Commands on this Dattabase'
ROLLBACK
END

Try and perform a CREATE, ALTER or DROP statement. The statement will be rollback.

We will now audit the database. For auditing database level trigger, we have a very special function called
‘EVENTDATA()’. Let’s alter the trigger we created above to see the audit data –

ALTER TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @AuditData XML
SET @AuditData=EVENTDATA()
END Try to drop the table and see the data returned by ‘EVENTDATA()’ –

The above XML is returned by the ‘EVENTDATA()’ function. Now let’s insert this data in a table so that we can design a report with all this data, at the end of the day –

Design the table first as shown below –

Now let’s alter a trigger as shown below –

ALTER TRIGGER NoDDLEventsOnDatabase
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @AuditData XML
SET @AuditData=EVENTDATA()
INSERT INTO AuditDLLStatements VALUES
(GETDATE(),CONVERT(NVARCHAR(50),@AuditData.query('data(//ServerName)')),CONVERT(NVARCHAR(50),@AuditData.query('data(//LoginName)')),CONVERT(NVARCHAR(50),@AuditData.query('data(//EventType)')),CONVERT(NVARCHAR(1000),@AuditData.query('data(//TSQLCommand)')))
END