T-SQL Tutorial

T-SQL DDL triggers

SQL Server DDL (Data Definition Language) triggers are special types of triggers that are activated in response to certain Data Definition Language (DDL) events. These events include CREATE, ALTER, and DROP statements for database objects such as tables, views, and stored procedures.

DDL triggers can be used to enforce business rules and maintain data integrity, as well as to audit and track changes to the schema of a database. They can also be used to prevent unauthorized changes to the schema by disabling certain DDL statements for specific users or roles.

DDL triggers are created using the CREATE TRIGGER statement, and are executed in response to a DDL event within the context of the current session.

Here is an example of a DDL trigger in SQL Server:

CREATE TRIGGER tr_PreventDelete
DECLARE @table_name nvarchar(255)
SELECT @table_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)')
IF @table_name = 'Test'
RAISERROR('Deleting the "Test" is not allowed.', 16, 1)

This trigger is named "tr_PreventDelete" and is created on the DATABASE level, it will fire in response to any DROP_TABLE event. Inside the trigger, it declares a variable @table_name and assigns the value of the table name that is being dropped, it then checks if the table name is "Test" and if it is, the trigger will raise an error message and rollback the DROP statement, preventing the table from being deleted.

It's important to note that DDL Triggers will fire only on DDL events that occured on the same server where the trigger is defined.