DDL Triggers in SQL Server

DDL triggers respond to DDL events like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. For example, you can define a DDL trigger that records CREATE or ALTER TABLE operations.

DDL trigger fires only after the events that fired them are executed successfully. They cannot be used as INSTEAD OF triggers.

You can create a DDL trigger to:

• Log changes made to the database schema;
• Prevent certain changes to the schema;
• To respond to any change in the database schema.

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

In the above syntax:

• trigger_name is the name of the new trigger being created.
• ON DATABASE specifies that the trigger is fired for DATABASE or ALL SERVER scoped events.
• ddl_trigger_option specifies the ENCRYPTION or EXECUTE AS clause. Encryption encrypts the trigger definition. EXECUTE AS defines the security context under which the trigger is executed.
• event_type specifies the event that causes the trigger to fire e.g., CREATE_TABLE, ALTER_TABLE, etc. The event_group is a group of event_type such as DDL_TABLE_EVENTS.

Let's create a DDL trigger that logs changes whenever a DB user creates, alters, or deletes tables.

First, create a database table TableLog to capture the logs, as shown below.

CREATE TABLE dbo.TableLog(
   LogID int IDENTITY(1,1) PRIMARY KEY,
   EventVal xml NOT NULL,
   EventDate datetime NOT NULL,
   ChangedBy SYSNAME NOT NULL
);

Now, create a trigger which will be fired every time a CREATE, ALTER, OR DROP table event occurs. The trigger will capture and log the event values into the TableLog table.

CREATE TRIGGER trgTablechanges
ON DATABASE
FOR	
    CREATE_TABLE,
    ALTER_TABLE, 
    DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TableLog
    (
       EventVal,
       DateChanged,
       ChangedBy
     )
        VALUES (
           EVENTDATA(),
           GETDATE(),
           USER
        );
END;

The above trgTablechanges trigger is fired whenever a table in the database is created, altered, or dropped. The EVENTDATA() value inserted into the EventVal field is an inbuilt function of the DDL trigger. It returns the transaction event details in XML format. The DDL triggers are created under Programmability -> Database Triggers, as shown below.

You can test the above trigger by creating a new table in the database. If the trigger is working as expected, then a new row is inserted into the TableLog table for the CREATE event. Now, create a new table called TestDDLTrigger.

CREATE TABLE dbo.TestDDLTrigger(
    LogID int IDENTITY(1,1) PRIMARY KEY,
    TestedBy SYSNAME NOT NULL
);

After creating the above trigger, select rows from the TableLog table and you will find a new entry for the TestDDLTrigger table, as shown below.

Clicking on the XML Event data in the EventVal column displays the event details as below

Thus, you can create a DDL trigger.