DDL Triggers are AFTER triggers

In this post I would like to look into DDL triggers and explain their functionality.

Let me start with a short introduction on triggers

All SQL Server developers use SQL triggers that basically is a mechanism that is invoked when a particular action occurs on a particular table.

Triggers consist of

• A name
• An action
• The execution

The maximum size of a trigger name is 128 characters.
The action of a trigger can be either a DML statement (INSERT, UPDATE, or DELETE) or a DDL statement (CREATE, ALTER, DROP)
Therefore, there are two trigger forms: DML triggers and DDL triggers.

The AFTER and INSTEAD OF options are two additional options that you can define for a trigger.

• AFTER triggers fire after the triggering action occurs.
• INSTEAD OF triggers are executed instead of the corresponding triggering action.
• AFTER triggers can be created only on tables, while INSTEAD OF triggers can be created on both tables and views

DDL triggers were introduced in SQL Server 2005. DDL triggers are not INSTEAD OF triggers.

They are implemented as AFTER triggers, which means the operation occurs and is then caught in the trigger.

The the operation can be  optionally rolled-back, if you put a ROLLBACK statement in the trigger body.
This means they’re not quite as lightweight as you might think. Imagine doing the following:

ALTER TABLE MyTable ADD newcolumn VARCHAR (30) DEFAULT ‘default value’;

If we have a DDL trigger defined for ALTER_TABLE events, or DDL_TABLE_EVENTS, the trigger due to the above T-SQL batch will fire and every row in the table will be expanded to include the new column (as it has a non-null default), and the operation is rolled back by your trigger body.

Type (copy paste) the following T-SQL statements in a new query window in the SSMS

CREATE DATABASE sampleDB;

GO


USE sampleDB;

GO

CREATE TABLE Product
 (
 Pid INT PRIMARY KEY
 IDENTITY ,
 pname NVARCHAR(50) ,
 price DECIMAL(18, 4)
 );

CREATE TRIGGER Not_Alter_Tables ON DATABASE
 FOR ALTER_TABLE
AS
 PRINT 'Not Alter Statements are permitted on the tables of this DB'
 SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
 'nvarchar(max)')
 RAISERROR ('Tables cannot be altered in this database.', 16, 1) 
 ROLLBACK;
GO

ALTER TABLE dbo.Product ADD newcolumn VARCHAR (30) DEFAULT 'default value'; 

Let me explain what I do in the code above.

I create a sample database. I create sample empty table. I create a trigger “Not_Alter_tables” on this database. This trigger applies to all the tables in the database.

This trigger is a DDL trigger, hence an AFTER trigger. In the trigger I capture the T-SQL statement that invoked the trigger, I print a statement, raise an error and then rollback the operation.

Then I attempt to add a new column in the table.

In this example, when the trigger is invoked, the table will be expanded to include the new column (as it has a non-null default), and then the operation is rolled in the trigger body.

Have a look at the picture below.

In a nutshell DDL triggers are AFTER triggers and can be quite expensive. In the example above the best way is not to use DDL triggers but to use instead explicit permissions e.g REVOKE/DENY to prevent users from altering the schema of the table.

Hope it helps!!!