Published on

February 28, 2007

Understanding DDL Triggers in SQL Server

DDL triggers are a powerful feature in SQL Server that allow you to audit and control changes happening to the database. Similar to DML triggers, which fire before or after UPDATE, DELETE, or INSERT commands, DDL triggers fire after the execution of CREATE, DROP, or ALTER commands.

There are several use cases for DDL triggers:

  • Audit DDL events: DDL triggers can be used to track and log changes made to the database or server.
  • Prevent changes: DDL triggers can be used to prevent certain changes from happening, such as creating tables or altering schemas.

It’s important to note that DDL triggers cannot be used as INSTEAD OF triggers, as they fire after the execution of DDL commands.

Here is an example of creating a DDL trigger:

CREATE TRIGGER test_ddl_trigger
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'Cannot create table due to insufficient space'
ROLLBACK;

In this example, the trigger is created on the database level and will fire whenever a CREATE TABLE command is executed. It prints a message and rolls back the transaction, preventing the table creation.

You can define the scope of a DDL trigger to be at the database level or server level. Database level events that can be audited include table events (create, alter, drop), view events (create, alter, drop), trigger events (create, alter, drop), synonym events (create, drop), index events (create, alter, drop), database level security events (create user, drop user, alter user, etc.), and service broker events (create message type, alter message type, drop message type, etc.). Server level events that can be audited include create database, drop database, create login, drop login, and alter login.

To query information about triggers, you can use the sys.triggers table for database scoped triggers and sys.server_triggers for server scoped triggers.

When a DDL trigger fires, you can access information about the event through the EVENTDATA function. This function provides details such as the event type, database name, T-SQL command text, and more. Here is an example:

CREATE TRIGGER trig_event_data
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE TABLE Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
RAISERROR('New tables cannot be created in this database.', 16, 1)
ROLLBACK;

In this example, the trigger prevents the user from creating tables in the database. The EVENTDATA function is used to retrieve the T-SQL command text that caused the trigger to fire.

You can modify the function to insert all create table events into a log table. First, create a table to maintain the log:

USE AdventureWorks;
GO
CREATE TABLE create_table_log (
  create_time datetime,
  DB_User nvarchar(100),
  Event_type nvarchar(100),
  TSQL nvarchar(2000)
);
GO

Then, create the trigger to insert the relevant information into the log table:

CREATE TRIGGER trig_create_table
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @data xml
SET @data = EVENTDATA()
INSERT INTO create_table_log
VALUES (
  GETDATE(),
  @data.value('(/EVENT_INSTANCE/LOGINNAME)[1]', 'nvarchar(100)'),
  @data.value('(/EVENT_INSTANCE/EVENTTYPE)[1]', 'nvarchar(100)'),
  @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(200)')
);
GO

Now, whenever a CREATE TABLE command is executed, a record will be inserted into the log table with the relevant information.

DDL triggers are a powerful way to audit and control changes made to the database and server. By using DDL triggers, you can track and log changes, prevent unauthorized modifications, and maintain data integrity.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.