Home » SQL DDL Trigger

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
)

Creating a DDL trigger to Capture the Events data

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

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

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
Also Read..




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 414 total views,  1 views today

Leave a Reply

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