Home » SQL DDL Trigger

SQL DDL Trigger

 SQL Server DDL Trigger


It 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 a database table.

First ,you need to create a table to store the information of changes on table.

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

 

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

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 

 

Lets check the implementation of DDL Trigger,  First we create a table in database .

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

 

When you create a table in database , A create_table event is executed and insert 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. It will open in a new query editor window.

select  * from table_logs

Now to check DDL trigger for Alter table Event, you need to execute a alter table command as given below.

Alter table tbl_check alter column name varchar(30)

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

As we see there are a new entry is added in log table for table alter.

select  * from table_logs

Now to check DDL trigger for Drop table Event, you need to execute a drop command as given below.

Drop table tbl_check

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

select  * from table_logs

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.