DDL Triggers in SQL Server

Introduction

In the first article (DML Trigger), we have gone through the triggers overview, use cases, types, and DML category in detail. In this article, we shall cover DDL Triggers. So, let’s begin.

DDL Triggers

DDL triggers are a type of trigger that gets fired and executed when DDL events occur on the database schema. DDL events include Create, Alter, Drop, Grant, Deny, and Revoke statements.

DDL triggers are mainly used to track structural changes in the database.

Syntax of DDL trigger

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR { event_type | event_group }
AS
    {sql_statement}

Note: we don’t need to specify a schema for a DDL trigger because it isn’t related to an actual database table or view.

DDL Trigger Event Type

• Create_Table
• Alter_Table
• Drop_Table

Now we shall create DDL Trigger. Let’s create a new table named Log_details.

CREATE TABLE log_details (
    log_id INT IDENTITY PRIMARY KEY,
    event_data XML NOT NULL,
    changed_by SYSNAME NOT NULL
);
GO

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

CREATE TRIGGER TR_ddl_tblChangeAlert
ON DATABASE
FOR       
    Create_table,
    Alter_table,
    Drop_table
AS
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO log_details (event_data, changed_by) VALUES (EVENTDATA(), USER);
    END
GO

The above trigger is fired whenever any table is created, altered, or dropped in the database.

In the above query, we used the EVENTDATA () function, which returns information about server or database events. It returns the transaction event details in XML format.

Below is the screenshot where created database trigger is showing.

Now let’s create a new table named Evt_table to test the above trigger. If the trigger is working as expected, a new row will insert into table log_details for creating events.

Create table Evt_table
(
Evt_id INT PRIMARY KEY IDENTITY(1,1),
Evt_name VARCHAR (50)
)

As you can see, the above table is created; now let’s check new row is inserted or not in table log_details.

select * from log_details;

In the above screenshot, we can find a new entry in the table log_details. We can see here that each detail of the above transaction has been included in the XML design.

Below is the screenshot showing details from the event_data column, which we through querying the log_details table.

 

Summary

Trigger is a very useful database object to keep track of database events. In this article, first we have gone through the DDL events types. Post that, we have gone through the DDL trigger detail. We shall cover the remaining trigger types in subsequent articles.