Sql Server - DDL triggers & EVENTDATA() function

DDL Trigger:

DDL triggers are fired whenever Data Definition Language (DDL) events  like Create, Alter, Drop, or Data Control Language (DCL) events   like Grant, Deny or Revoke occurs.

 DDL Triggers is useful for the following purpose:

Can be used by DBA ‘s:

• to Issue an alert when a DDL event occurs in Database or Server.
• to log information about database-level DDL events and Server-level DDL events.
• to Secure the database or server from malpractice.

DDL triggers scope is of two types:

1.)Server level

2.)Database level

Refer below link,for more information about different  Scope of DDL triggers.

http://msdn.microsoft.com/en-us/library/ms189871%28v=sql.90%29.aspx

By executing below query,we can get hierarchy list of DDL events & DDL grouped events.

WITH DirectReports(name, parent_type, type, level, sort) AS
(
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
    FROM sys.trigger_event_types
    WHERE parent_type IS NULL
    UNION ALL
SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),
e.parent_type, e.type, level + 1,
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)
    FROM sys.trigger_event_types AS e
        INNER JOIN DirectReports AS d
        ON e.parent_type = d.type
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;

Sql server gives flexibility to handle DDL events in DDL triggers,you can see that in the highlighted section of below image.

For example in below image ,

 

Rownumber 99 – DDL_PROCEDURE_EVENTS is to handle all events related to procedure in trigger.

In rownumber 100,101,102 - ALTER_PROCEDURE,CREATE_PROCEDURE & DROP_PROCEDURE is to handle required procedure related events separately in trigger.

Example : DDL trigger for DDL events

Note: Login with sa credentials to test below examples

In SSMS,
under Object Explorer  in the Programmability -- >> Database Triggers ,you can find
Database level DDL triggers. 
under Object Explorer  in the Server Objects -- >>Triggers ,you can find  server level triggers

--step 1:create DDL trigger for handling table events
CREATE TRIGGER Procedure_event
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE,CREATE_TABLE
AS
PRINT 'disable trigger [Procedure_event] to drop,create or alter tables'
ROLLBACK;
GO

--step 2: checking,you cannot create,alter & drop tables
CREATE TABLE TEST_TABLE_EVENT (A INT)
GO
ALTER TABLE TEST_TABLE_EVENT ADD B BIGINT
GO
DROP TABLE TEST_TABLE_EVENT
GO

--step 3: drop triggerDROP TRIGGER Procedure_event ON DATABASE
GO

--step 4: after dropping trigger ,you can create,alter & drop tables

CREATE TABLE TEST_TABLE_EVENT (A INT)
GO
ALTER TABLE TEST_TABLE_EVENT ADD B BIGINT
GO
DROP TABLE TEST_TABLE_EVENT
GO

Example : DDL trigger for grouped DDL events

--step 1:create DDL trigger for handling table grouped events
CREATE TRIGGER Procedure_grouped_event
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'disable trigger [Procedure_grouped_event] to drop,create or alter tables'
ROLLBACK;
GO

--step 2: checking,you cannot create,alter & drop tables
CREATE TABLE TEST_TABLE_EVENT (A INT)
GO
ALTER TABLE TEST_TABLE_EVENT ADD B BIGINT
GO
DROP TABLE TEST_TABLE_EVENT
GO

--catalog view to see the trigger events created
SELECT * FROM sys.trigger_events

--step 3: drop trigger
DROP TRIGGER Procedure_grouped_event ON DATABASE
GO

--step 4: after dropping trigger ,you can create,alter & drop tables
CREATE TABLE TEST_TABLE_EVENT (A INT)
GO
ALTER TABLE TEST_TABLE_EVENT ADD B BIGINT
GO
DROP TABLE TEST_TABLE_EVENT
GO

EVENTDATA() function:

EVENTDATA() can be used inside the body of a DDL or logon trigger and returns data only when referenced directly inside of a DDL or logon trigger.

EVENTDATA() – captures information about an event that initiated a DDL trigger to fire.

EVENTDATA() returns data in the form of XML.

EVENTDATA() returns null if it is called by other routines, even if those routines are called by a trigger.

Used to log information about database-level DDL events and Server-level DDL events

Sample output of EVENTDATA():

Use  xquery to get values from XML fragment  and insert into log table.

<EVENT_INSTANCE>
  <EventType>CREATE_DATABASE</EventType>
  <PostTime>2012-08-27T23:03:58.620</PostTime>
  <SPID>53</SPID>
  <ServerName>SERVER NAME </ServerName>
  <LoginName>sa</LoginName>
  <DatabaseName>TEST_SERVERLEVEL_EVENT</DatabaseName>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE DATABASE TEST_SERVERLEVEL_EVENT
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

Examples:

--step 1: create log table
USE MASTER
GO

CREATE TABLE Trigger_LogTable (
   PostTime DATETIME,
   LoginName NVARCHAR(100),
   EventType NVARCHAR(100),
   TSQLCommand NVARCHAR(2000) 
)

GO

--step 2: create server level DDL trigger

CREATE TRIGGER all_server_level_events
ON All Server
FOR
DDL_SERVER_LEVEL_EVENTS
AS   

      DECLARE @Eventdata XML
      SET @Eventdata = EVENTDATA()

      --SELECT @Eventdata.query('//EVENT_INSTANCE')

      INSERT dbo.Trigger_LogTable (PostTime, LoginName, EventType, TSQLCommand)
      VALUES  (GETDATE(), 
      CONVERT(NVARCHAR(100), CURRENT_USER), 
      @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
      @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )

--step 3: create server level event  
CREATE DATABASE TEST_SERVERLEVEL_EVENT

--check in log table
USE MASTER
GO

SELECT * FROM dbo.Trigger_LogTable

--catalog view to see server level trigger events
SELECT * FROM SYS.SERVER_TRIGGER_EVENTS

You can also use below query to view the dependencies of  server-level triggers

USE MASTER;
GO
 
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE referencing_class = 13;

--DROP TRIGGER all_server_level_events ON ALL SERVER
--DROP DATABASE TEST_SERVERLEVEL_EVENT
--DROP TABLE dbo.Trigger_LogTable