DDL тригеров.

Номера групп событий показывают подчиненность одной группы другой (например, группа 1.1 подчинена(вложена) в группу 1 и т.д.). Группы событий с номерами 1 – 1.2.3 описывают события для всего сервера, а остальные группы событий – событий уровня БД.

Для LOGON триггеровуказывается ключевое слово LOGON.

6. WITH APPEND. Применяется только для DML триггеров и показывает, что можно добавить дополнительный триггер существующего типа (реагирующий на определенный набор действий из множества INSERT, UPDATE, DELETE). Эта опция необходима для уровня совместимости 65 и ниже, в случае же использования уровня совместимости 70 и выше она используется по умолчанию (Microsoft обещает убрать из синтаксиса команды эту опцию в новых версиях MS SQL Server).

7. NOT FOR REPLICATION. Эта опция применяется только для DML триггеров и показывает, что триггер не будет запускаться во время процесса репликации (дублирования) данных.

8. AS – ключевое слово, за которым идут команды, составляющие тело триггера. Если команд более одной, то они должны быть взяты в операторные скобки BEGIN…END, обозначающие, что эти команды являются одним блоком (пакетом). В теле DML триггеровнедопустимо использование следующих команд: ALTER DATABASE, CREATE DATABASE, DROP DATABASE, LOAD DATABASE, RESTORE DATABASE, LOAD LOG, RESTORE LOG, RECONFIGURE. Кроме того, следующие команды недопустимы в теле DML триггеров, когда они относятся к таблице или представлению, на которой(ым) выполнилось (должно было выполниться) действие, которое привело к активации триггера (это ограничение введено в MS SQL Server 2005, однако оно действует, если уровень совместимости установлен в значение 80): CREATE INDEX, ALTER INDEX, DROP INDEX, DBCC DBREINDEX, ALTER PARTITION FUNCTION, DROP TABLE, ALTER TABLE (Когда эта команда выполняет одно из следующих действий: добавляет, модифицирует или удаляет столбец, переключает разделы(partition), добавляет или удаляет ограничения PRIMARY KEY или UNIQUE).

9. EXTERNAL NAME. После этих ключевых слов идет полное имя (включающее имя сборки и имя класса) метода, который написан на языке высокого уровня, поддерживающем CLR, и является телом триггера.

При написании тела DML триггеров можно использовать специальные функции COLUMNS_UPDATED() и UPDATE(имя_столбца), а также две специальные таблицы inserted и deleted. Их применение позволяет определить, какое действие вызвало активацию триггера, если триггер может быть активирован различными командами. В случае же использования INSTEAD OF триггера данные в таблице или представлении не изменяются, так как триггер вызван вместо активировавшего его действия. Использование же таблиц inserted и deleted позволяет определить, как данные должны были бы измениться.

Функция COLUMNS_UPDATED() возвращает значение типа varbinary, представляющее собой набор байт, каждый из которых представляет информацию об изменении значения группы из 8-ми столбцов. При этом 1-ый байт представляет эту информацию для 1-ой группы из 8-ми столбцов и т.д. В каждом байте младший бит (нулевой) представляет информацию об изменении 1-ого столбца группы, 1-ый бит – 2-ого столбца и т.д. Если какой-либо столбец изменился, то соответствующий ему бит будет равен 1, иначе 0. Функция COLUMNS_UPDATED() выдает одни и те же значения независимо от того, изменилось ли в результате выполнения команды UPDATE хотя бы одно значение в обновляемых столбцах или нет. При активации триггера командой INSERT функция COLUMNS_UPDATED() возвращает значение, соответствующее изменению ВСЕХ столбцов таблицы или представления.

Функция UPDATE(имя_столбца) ведет себя аналогично функции COLUMNS_UPDATED(), однако имеет следующие отличия: применима только к одному столбцу и возвращает значение TRUE, если столбец изменился, и FALSE – в противном случае. Эту функцию можно последовательно применять в уловном операторе IF для проверки изменения нескольких столбцов.

Таблицы inserted и deleted являются временными хранящимися в памяти таблицами, создаваемыми при запуске DML триггера автоматически. Их структуру и данные в них нельзя изменять, создавать индексы для этих таблиц также недопустимо, их использование возможно ТОЛЬКО в теле DML триггеров. Таблица deleted содержит строки, которые были удалены или строки, которые были обновлены командой UPDATE, причем в последнем случае для обновляемых столбцов содержатся их первоначальные значения. Таблица inserted содержит добавляемые в таблицу строки (команда INSERT) или обновляемые строки, причем в последнем случае в обновляемых столбцах этой таблицы содержатся новые значения. Следует отметить, что для INSTEAD OF триггеров получить измененные/добавленные значения можно получить ТОЛЬКО из таблицы inserted, а набор удаляемых строк – ТОЛЬКО из таблицы deleted. Если в результате выполнения какой-либо из команд, которая должна была привести к срабатыванию триггера, ни одна строка (значение в строке) в таблице (представлении) не была изменена, то триггер все равно будет вызван. При этом количество строк в таблицах inserted и deleted будет равно 0.

В MS SQL Server 2005 не допускаются ссылки на столбцы с типами данных text, ntext и image в таблицах inserted и deleted для AFTER триггеров, в то время как типов данных varchar(max), nvarchar(max), varbinary(max) это ограничение не касается.

Таким образом, можно сформулировать следующие правила, по которым можно определить какое действие активировало триггер, если триггер может быть активирован несколькими командами:

1. Если функция COLUMNS_UPDATED() вернула значение 0, то триггер был активирован командой удаления(DELETE), соответственно количество строк в таблице deleted будет соответствовать количеству удаляемых строк.

2. Если функция COLUMNS_UPDATED() вернула значение не 0, то триггер был активирован либо командой INSERT, либо командой UPDATE.

2.1. Если в таблице inserted - ненулевое количество строк, а таблица deleted – не содержит строк, то триггер был активирован командой INSERT.

2.2. Если таблицы inserted и deleted содержат одинаковое ненулевое количество строкили таблицы inserted и deleted содержат нулевое количество строк и функция COLUMNS_UPDATED() вернула некоторое значение , удовлетворяющее неравенству , где - количество столбцов в таблице или в представлении, то триггер был активирован командой UPDATE.

2.3. Если таблицы inserted и deleted содержат нулевое количество строк и функция COLUMNS_UPDATED() вернула некоторое значение , такое что , то определить какая из команд INSERT или UPDATE активировала триггер не представляется возможным, т.к. команда UPDATEможет изменять значения во всех столбцах, а команда INSERT может не вставить ни одной строки.

При написании DDL триггеров, как и в случае с DML триггерами, возникает необходимость определения того, какое именно событие активировало триггер. Для этих целей используется функция EVENTDATA(). Эта функция может быть использована только в теле DDL или LOGON триггера (в противном случае она возвращает NULL, даже если используется в хранимой процедуре, которую вызывает соответствующий триггер). Данные, возвращаемые этой функцией, теряют свою актуальность, если произошел откат транзакции, в которой была вызвана функция EVENTDATA().

Следует отметить, что эта функция возвращает результат своей работы в виде xml данных, представленных в юникоде. При этом допускается использование только следующих кодов символов: 0x0009, 0x000A, 0x000D, 0x0020..0xD7FF, 0xE000..0xFFFD. Все остальные символы считаются недопустимыми и отображаются с помощью символа ?.

Функция EVENTDATA() возвращает xml данные, содержащие информацию о типе события, активировавшего триггер, времени его возникновения, SPID (System Process ID) соединения, когда триггер был вызван. В зависимости от типа события может возвращаться дополнительная информация о нем, например: название базы данных, в которой это событие произошло, название объекта, которого это событие касается, команда T-SQL, которая привела к возникновению этого события.

Все эти данные передаются внутри элемента <EVENT_INSTANCE>…</EVENT_INSTANCE>. Например, для события ALTER_TABLE этот элемент имеет следующий вид:

<EVENT_INSTANCE>

<EventType>type</EventType>

<PostTime>date-time</PostTime>

<SPID>spid</SPID>

<ServerName>name</ServerName>

<LoginName>name</LoginName>

<UserName>name</UserName>

<DatabaseName>name</DatabaseName>

<SchemaName>name</SchemaName>

<ObjectName>name</ObjectName>

<ObjectType>type</ObjectType>

<TSQLCommand>command</TSQLCommand>

</EVENT_INSTANCE>

Для других событий могут изменяться названия элементов и их количество, если, информация, возвращаемая для события, отличается от информации, возвращаемой для события ALTER_TABLE. В документации для каждого события описан перечень всех его элементов и типы данных этих элементов.

При создании | изменении триггеров нужно учитывать, что:

1. Команда {CREATE | ALTER} TRIGGER должна быть 1-ой командой в пакете команд.

2. Триггер создается только в текущей БД, однако может ссылаться на объекты из других БД.

3. Команда SET может быть использована в теле триггера, но все установки, сделанные с ее помощью имеют силу только на время работы триггера. По его завершению все настройки автоматически возвращаются в первоначальное состояние.

4. Рекомендуется не использовать в триггере команд, которые приводят к выводу данных (например, SELECT – в случае, когда она не является под запросом, и PRINT).

5. Для AFTER триггеров с помощью процедуры sp_settriggerorder можно установить 1-ый и последний триггер, который будет срабатывать как реакция на выбранное действие. Вызов этой процедуры имеет следующий вид:

sp_settriggerorder [ @triggername = ] '[ пространство_имен. ] имя_триггера'

, [ @order = ] 'значение'

, [ @stmttype = ] 'тип_команды'

[ , [ @namespace = ] { 'DATABASE' | 'SERVER'| NULL } ]

где @triggername –задает имя триггера, причем пространство имен может быть указано только для DML триггеров.

@order –определяет последовательность срабатывания триггера, имеет тип данных varchar(10) и может принимать значения: ‘First’ – триггер срабатывает первым, ‘Last’ – триггер срабатывает последним, ‘None’ – порядок срабатывания триггера не определен. Следует отметить, что первый и последний триггеры должны ОБЯЗАТЕЛЬНО отличаться и можно описать только один первый и один последний триггеры для какого-либо действия.

@stmttype –описывает тип действия, для которого устанавливает порядок выполнения триггеров, имеет тип данных varchar(50) и может иметь следующие значения: ‘INSERT’, ‘UPDATE’, ‘DELETE’, ‘LOGON’ или название любого из типов событий, допустимых при создании DDL триггера.

@namespace –задает уровень DDL триггера: DATABASE или SERVER. Для LOGON триггеров можно указать только SERVER, для DML триггеров – либо не указывать последний параметр, либо указать значение NULL.

Следует отметить, что при изменении триггера, имеющего атрибут First или Last командой ALTER TRIGGER, этот атрибут автоматически сбрасывается и требует своей повторной установки с помощью процедуры sp_settriggerorder.

6. При создании триггеров в их теле допускается обращаться к таблицам, которых еще нет в момент создания триггера. Однако, в момент срабатывания триггера такие таблицы должны быть уже созданы, иначе будет получено сообщение об ошибке.

7. Допускается создание рекурсивных триггеров, если опция RECURSIVE_TRIGGERS для БД установлена в значение enabled. Рекурсия бывает 2-ух видов прямой и косвенной. Прямая рекурсия, например, имеет место в случае, когда приложение изменяет значения в каком-то столбце таблицы, что приводит к срабатыванию триггера, который, в свою очередь, также меняет значения в каком-то столбце таблицы, что опять приводит к срабатыванию этого же триггера и т.д. Косвенная рекурсия, например, имеет место в случае, когда приложение изменяет значения в каком-то столбце таблицы T1, что приводит к срабатыванию триггера TR1, который, в свою очередь изменяет значения в каком-либо столбце таблицы T2, что приводит к срабатыванию триггера TR2, который изменяет значения в каком-либо столбце таблицы T1, что опять приводит к срабатыванию триггера TR1 и т.д. Для того, чтобы отключить косвенную рекурсию, необходимо установить значение опции сервера nested triggers (отвечает за возможность косвенной рекурсивных вызовов, количество которых не может превышать 32) в 0 (что также приведет к отключению прямой рекурсии для триггеров). Эта опция не влияет на поведение INSTEAD OF триггеров. По умолчанию косвенная рекурсия разрешена (значение 1).

При создании | изменении DML триггеров нужно также учитывать, что:

1. Триггер может быть связан только с одной таблицей или представлением.

2. Для представления можно создать ТОЛЬКО INSTEAD OF триггер.

3. INSTEAD OF триггер не может быть создан для таблицы, которая имеет ограничение FOREIGN KEY с опцией CASCADE для действий DELETE или UPDATE.

4. Если при создании триггера было указано пространство имен, то в теле триггера при обращении к используемым объектам также необходимо указывать их пространство имен.

5. Для таблицы или представления может быть описан триггер, который реагирует сразу на несколько команд.

6. Может быть описано несколько триггеров, реагирующих на один и тот же набор команд, при этом они будут вызывать в порядке их создания.

7. SQL Server не поддерживает пользовательские триггеры для системных таблиц, поэтому не рекомендуется такие триггеры создавать.

8. Команды TRUNCATE TABLE и WRITETEXT не приводят к срабатыванию триггеров.

При создании | изменении DDL триггеров нужно также учитывать, что:

1. DDL триггеры не привязаны к пространству имен (schema), следовательно, для получения метаданных об этих триггерах не могут быть использованы функции OBJECT_ID, OBJECT_NAME, OBJECT_PROPERTY и OBJECT_PROPERTYEX. Вместо этого нужно использовать представления sys.triggers, sys.trigger_events, sys.sql_modules, sys.assembly_modules для триггеров уровня БД и представления sys.server_triggers, sys.server_trigger_events, sys.server_sql_modules, sys.server_assembly_modules для триггеров уровня сервера.

2. DDL триггеры не срабатывают вследствие действий, касающихся локальных и глобальных временных таблиц и хранимых процедур.