SQL DDL Trigger

 SQL Server DDL Trigger is a special kind of trigger which fires in response to DDL statements.

DDL Triggers can be of two scopes:

1. Server Level Trigger – By creating Trigger on Server, you can capture events for all Databases on the Server,

2. Database Level Trigger – This scope is limited to a single Database on which the Trigger is created.

It can capture the information about the EVENT that fired it by using EVENTDATA() function. EVENTDATA() returns an XML .

SYNTAX

CREATE TRIGGER triggername

ON { DATABASE |  ALL SERVER}

FOR { eventtype | eventgroup }

AS {

--sqlstatement

}

Lets look at an example of DDL Trigger to capture the changes on database table.

First, we will Create a table named table_logs that will store the information changes on tables in SQL Server Database.

CREATE TABLE table_logs (
log_id INT IDENTITY(1,1) PRIMARY KEY,
event_log XML NOT NULL,
changed_by SYSNAME NOT NULL
)

Lets create a DDL trigger to track changes in table and insert events data into the table_logs table.

Following statement creates a DDL trigger named trg_table_changes that will capture the events data for table when any table in created, altered or dropped from SQL Server Database and insert log into table table_logs.

CREATE TRIGGER trg_table_changes
ON DATABASE
FOR
CREATE_TABLE,
ALTER_TABLE, 
DROP_TABLE
AS
BEGIN
SET NOCOUNT ON ;INSERT INTO table_logs (
event_log,
changed_by
)
VALUES (
EVENTDATA(),
USER
)
END 

After creating the trigger, Lets check the implementation of DDL Trigger.

Lets create a new table named tbl_check in database.

Create table tbl_check ( sno int identity(1,1), name varchar(50))

You will see that once you create a table in database, a create_table event is executed and inserts a changes into table_logs table .

As you can see , an event_log is a XML type , to see all the log information just click on xml link in result grid. It opens in a new query editor window.

select  * from table_logs

Now we will check DDL trigger for Alter table Event for this you need to execute a alter table statement as given below.

Alter table tbl_check alter column name varchar(30)

Lets check the log table to verify whether it tracks the changes for Alter table Event or not.

As you see there is a new entry in log table for table alter.

select  * from table_logs

Drop table tbl_check

As you can see there is one more new entry is showing for Drop table event in log table.

select  * from table_logs