Миф: Триггеры DDL являются триггерами INSTEAD OF

Автор: Paul Randal, A SQL Server DBA myth a day: (4/30) DDL triggers are INSTEAD OF triggers

Триггеры DDL (введённые в SQL Server 2005) являются триггерами INSTEAD OF

ЛОЖЬ

Триггеры DDL реализованы как триггеры AFTER, что означает, что операция сначала выполняется, а затем перехватывается триггером (и при необходимости откатывается, если в теле триггера есть оператор ROLLBACK).

Это означает, что они не столь легковесны, как можно было бы предположить.

Представьте следующее:

ALTER TABLE MyBigTable ADD MyNewNonNullColumn VARCHAR (20) DEFAULT 'Paul';

Если определён триггер DDL для событий ALTER_TABLE или, возможно, даже более общего, например DDL_TABLE_EVENTS, каждая строка в таблице будет расширена для включения нового столбца (поскольку у него есть значение по умолчанию, отличное от NULL), затем сработает триггер, и операция будет откачена кодом в теле триггера. Совсем не идеально. (Попробуйте сами и посмотрите в журнале с помощью fn_dblog — вы увидите откат операции).

Что было бы лучше в данном случае — это явно предоставить (GRANT) или запретить (DENY) разрешение ALTER или сделать что-то вроде разрешения DDL-операций только через созданные вами хранимые процедуры.

Тем не менее, триггеры DDL действительно позволяют эффективно предотвратить выполнение операции, но относительно дорогим способом. И они позволяют проводить аудит того, кто что сделал, так что я не утверждаю, что они бесполезны — просто будьте осторожны.

У Кимберли есть отличная статья о триггерах DDL: "EXECUTE AS" and an important update your DDL Triggers (for auditing or prevention).