SQL Server misunderstanding 30th on the 4th day DDL triggers are instead OF triggers _mssql

Myth #4: DDL triggers (introduced after SQL Server 2005) are instead OF triggers

This is wrong.

The implementation principle of DDL triggers is actually an after trigger. This means that the DDL operation occurs first, and then the trigger catches the action (of course, if you write a rollback in the trigger, it can be rolled back).

The presence of rollback also means that the trigger is not as lightweight as you might think, and look at the following example:

ALTER TABLE mybigtable ADD mynewnonnullcolumn VARCHAR () DEFAULT ' Paul '

If there is a DDL trigger for the defined for Alter_table event, or a broader event such as ddl_table_events. The above DDL code will add new columns to each row of data in the table, triggering the trigger action. If there is a rollback in your trigger that prevents the DDL operation from happening, the price is not small (don't believe it).

Of course, a better approach would be to set grant or deny permissions on alter, or simply allow DDL operations through the stored procedures you create.

However, although DDL triggers can achieve the purpose of preventing DDL operations, they are costly. The advantage of DDL triggers is that they allow you to record certain people doing some sort of modification, so I'm not saying that DDL triggers are not allowed, but that they should be used with care.

Kimberly has a very good blog post about DDL triggers: "EXECUTE as" and an important update your DDL triggers (for auditing or prevention).