CREATE TRIGGER назва_тригера

ON { ALL SERVER | DATABASE }

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF | {‘ім'я_користувача’ | ‘login_name’}}

}[,]

]

{ FOR [AFTER]}

{тип_події | група подій}[,]

AS

{

Перелік_команд_SQL |

EXTERNAL NAME ім'я_складання.ім'я_класу.назва_методу [;]

}

LOGON тригер:

CREATE TRIGGER назва_тригера

ON ALL SERVER

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF ‘login_name’}

}[,]

]

{ FOR [AFTER]} LOGON

AS

{

Перелік_команд_SQL |

EXTERNAL NAME ім'я_складання.ім'я_класу.назва_методу [;]

}

У випадку зміни вже існуючого DDL або LOGON тригера замість команди CREATE необхідно використовувати команду ALTER.

Розглянемо більш докладно синтаксис створення | зміни тригерів, причому слід зазначити подібність синтаксису команди CREATE | ALTER при створенні | зміні для всіх видів тригерів:

1. ON ім'я_об'єкта. Ця секція команди показує для якого об'єкта(ів) створюється тригер. У випадку DML тригерів необхідно вказати або ім'я таблиці (не можна вказувати локальні або глобальні тимчасові таблиці), або ім'я подання, причому для подання можна створити тільки INSTEAD OF тригер (виконується замість обраної дії(й)). Для DDL тригерівяк ім'я об'єктаможна вказати або рівень бази даних (DATABASE), або рівень сервера БД (ALL SERVER). Залежно від обраного рівня будуть доступні різні події (групи), для яких можна створити тригер. Для LOGON тригерівяк ім'я об'єктаможна вказати тільки рівень сервера БД (ALL SERVER).

2. WITH список_опцій. Використання опції ENCRIPTION приводить до того, що текст команди створення тригера шифрується, що не дозволяє його переглядати. Ця опція не може бути зазначена для CLR тригерів (тригерів, написаних мовою, що підтримує технологію .NET Framework).

3. EXEC[UTE] AS. Визначає контекст виконання для тригера. Можливі наступні опції: CALLER (значення за замовчуванням) - показує, що всі команди в модулі (тригері) виконуються від імені користувача, дії якого привели до виклику тригера. Слід зазначити, що цей користувач повинен мати відповідні права на доступ не тільки до тригера, але й до всіх об'єктів, з якими тригер працює під час свого виконання. SELF - еквівалентно вказівці імені користувача, що створив або змінив даний тригер. OWNER - показує, що всі команди даного тригера виконуються в контексті (від імені) користувача, що є власником тригера. Якщо тригер не має власника, то ним є той користувач, що є власником схеми даних, у яку входить тригер. Ця опція не може бути використана для DDL і LOGON тригерів. ІМ'Я_КОРИСТУВАЧА | LOGIN_NAME - указує ім'я користувача (для LOGON тригерів необхідно використовувати ім'я login(ім'я, під яким користувач з'єднується із СУБД)) від імені якого будуть виконуватися команди тригера. Цей користувач повинен мати відповідні права доступу до використовуваних об'єктів.

4. {FOR [AFTER] | INSTEAD OF} – показує, коли буде виконаний тригер: після дії, на яку тригер реагує (FOR [AFTER], причому слово AFTER є необов'язковим) або замість цієї дії (INSTEAD OF). Слід зазначити, що DML тригери для подань можуть бути тільки INSTEAD OF, у той час як DDL і LOGON тригери можуть виконувати тільки після дії (FOR [AFTER]).

5. ДІЯ показує в якому випадку(ах) тригер буде виконуватися. Для DML тригерівдією може бути виконання однієї з команд INSERT, UPDATE, DELETE, причому тригер може реагувати як на одну з перерахованих команд, так і на будь-яку пару команд або навіть на всі команди. Для DDL тригеріввказується ідентифікатор групи подій (нумерація груп проставлена з урахуванням вкладеності груп) або події (Табл.1):

Таблиця 1.

Перелік ідентифікаторів груп подій і подій,

використовуваних при створенні (зміні) 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), vrabinary(max) це обмеження не стосується.

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

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

2. Якщо функція COLUMNS_UPDATED() повернула значення не 0, то тригер був активований або командою INSERT, або командою UPDATE.

Якщо в таблиці inserted - ненульова кількість рядків, а таблиця deleted – не містить рядків, то тригер був активований командою INSERT.

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

Якщо таблиці 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 тригери не спрацьовують внаслідок дій, що стосуються локальних і глобальних тимчасових таблиць і збережених процедур.

Видалення тригерів.

Розглянемо команду видалення тригерів DROP TRIGGER.

DML тригер:

DROP TRIGGER простір_имен.ім'я_тригера1[, простір_имен.ім'я_тригера2...] [;]

DDL тригер:

DROP TRIGGER ім'я_тригера1[, ім'я_тригера2...]

ON {DATABASE | ALL SERVER}

[;]

LOGON тригер:

DROP TRIGGER ім'я_тригера1[, ім'я_тригера2...]

ON ALL SERVER

причому ключове слово DATABASE показує, що DDL тригер(и) мають рівень БД, а ключове слово ALL SERVER показує, що DDL тригер(и) мають рівень сервера.

Слід зазначити, що:

1. Знищення таблиці знищує і всі пов'язані з нею DML тригери.

2. Коли тригер знищується, то інформація про нього з подань sys.objects, sys.triggers, sys.sql_modules також віддаляється.

3. Декілька DDL тригерів можна видалити однією командою DROP TRIGGER у тому випадку якщо всі вони мають або рівень БД, або рівень сервера.

4. Для того щоб переіменувати тригер, необхідно виконати відповідні команди DROP TRIGGER і CREATE TRIGGER.

Дозвіл | заборона спрацьовування тригерів.

Розглянемо команди дозволу | заборони спрацьовування тригерів.

{ENABLE | DISABLE} TRIGGER

{[простір_імен.]ім'я_тригера1[,[простір_імен.]ім'я_тригера1...] | ALL}

ON {ім'я_об'єкта | DATABASE | ALL SERVER} [;]

причому:

1. Простір_імен можна вказати тільки для DML тригерів.

2. Ключове слово ALL показує, що всі тригери зазначеного типу (тип вказується після ключового слова ON) будуть дозволені або заборонені для виконання.

3. Після ключового слова ON можна вказати або ім'я_таблиці|ім’я_подання для DML тригерів, або ключове слово DATABASE для DDL тригерів рівня БД, або ключове слово ALL SERVER для DDL тригерів рівня сервера або для LOGON тригерів.

4. Команда DISABLE TRIGGER, не може використовуватися в тілі тригера, стан якого вона повинна змінити, якщо в ньому відбувається виконання дії, що приводить до повторної активації тригера в явному вигляді (без використання команди EXECUTE або процедури sp_executesql). Варто також ураховувати, що команди {ENABLE | DISABLE} TRIGGER, повинні бути першими в пакеті команд (перебувати відразу після begin або go).

Для дозволу | заборони виконання DML тригерів стосовно до таблиць також можна використовувати команду ALTER TABLE, що у цьому випадку має такий вигляд:

ALTER TABLE [ ім'я_БД.[простір_імен]. | простір_імен.] ім'я_таблиці

{ENABLE | DISABLE } TRIGGER

{ALL | ім'я_тригера1[, ім'я_тригера2...]

де ім'я таблиці може задаватися з використанням імені БД, у якій таблиця перебуває і простору імен, а тригер задається своїм ім'ям. При цьому однією командою можна дозволити | заборонити виконання декількох тригерів, заданих своїми іменами або відразу всіх тригерів, якщо використане ключове слово ALL.

Команда ALTER TABLE ім'я_таблиці DISABLE TRIGGER {ім'я_тригера | ALL} і ALTER TABLE ім'я_таблиці ENABLE TRIGGER {ім'я_тригера | ALL} не можуть одночасно використовуватися в тілі тригера, стан якого вони змінюють. Крім цього команда ALTER TABLE ім'я_таблиці DISABLE TRIGGER {ім'я_тригера | ALL} не може бути застосована в таких випадках, коли в тілі DML тригера, відключення спрацьовування якого вона повинна забезпечити, відбувається виконання дії, що приводить до повторної активації тригера в явному виді (без використання команди EXECUTE або процедури sp_executesql).

Приклади тригерів.

Всі приклади тригерів, робота яких стосується таблиць, будуть використовувати таблицю table1, що створена наступною командою: