Основи тригерів

Тригери SQL Server запускаються один раз при виконанні якої-небудь операції модифікації даних Такий режим роботи відрізняється від Oracle, де тригери можуть виконуватися як для кожної операції, так і для кожної окремої оброблюваної нею рядка Незважаючи на те що на перший погляд це

здається обмеженням, необхідність розробки пакетних тригерів забезпечує прозору логіку, так само як і гідну продуктивність

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

У SQL Server 2005 існують два типи тригерів транзакцій: INSTED OF (тригер заміни операції) і AFTER (тригер, що виконується відразу після операції) Ці типи відрізняються своїм призначенням, часом виконання і виробленим ефектом Все це деталізовано в табл 231

Тригери бази даних можуть запускатися і командами мови визначення дан-Новинка ^ нь, х DD*” — CREATE> alter і drop Оскільки ці тригери оперують на рівнях-2005 НЕ схеми бази даних, вони розглянуті в главі 17

Таблиця 231 Порівняння типів тригерів

Тригер INSTEAD OF

Тригер AFTER

Інструкція DML

Автоматично відкочується

Виконується, якщо тригер сам не

відкотить транзакцію

Час виконання

Перед обмеженнями первинного та

Після виконання транзакції, але

зовнішнього ключів

перед її підтвердженням

Кількість можливих подій

Одне

Кілька

таблиці

Чи можна застосовувати до пред

Так

Ні

ставлені

Чи припустима вкладеність

Залежно від параметрів сер

Залежно від параметрів

віра

сервера

Чи припустима рекурсивность

Ні

Залежно від параметрів

сервера

Порядок виконання транзакцій

Створення тригерів вимагає чіткого розуміння загального порядку виконання транзакцій, в іншому випадку може виникнути конфлікт між обмеженнями та тригерами, що перетворить розробку програми та його налагодження в кошмар

Будь-яка транзакція проходить кілька перевірок і програмних кодів в наступному порядку

1 Перевірка Identity Insert

2 Обмеження допустимості порожніх значень

3 Перевірка типу даних

4 Виконання тригера INSTEAD OF Якщо такий тригер існує, то виконання інструкції DML зупиняється в цій точці Тригери INSTEAD OF не можуть бути рекурсивними Таким чином, якщо тригер, запущений подією деякої інструкції, знову виконує ту ж інструкцію (INSERT, UPDATE або DELETE), вдруге його присутність ігнорується

5 Обмеження первинного ключа

6 Обмеження перевірки

7 Обмеження зовнішнього ключа

8 Виконання інструкції DML і оновлення журналу транзакцій

9 Виконання тригера AFTER

10 Підтвердження транзакції

11 Запис у файл даних

На основі знання порядку виконання транзакцій можна зробити кілька зауважень, що стосуються проектування тригерів

■ Тригер AFTER виконується після всіх обмежень З цієї причини він не може скоригувати дані, тому дані повинні пройти всі перевірки обмеженнями, включаючи обмеження зовнішнього ключа

■ Тригер INSTEAD OF може допомогти обійти проблеми, повязані із зовнішнім ключем, але не з допустимостью порожніх значень, типом даних або стовпцем ідентичності

■ Тригер AFTER може брати до уваги, що дані вже пройшли всі вбудовані перевірки цілісності даних

■ Тригер AFTER виконується перед підтвердженням транзакції Таким чином, якщо дані неприпустимі, він має можливість відкотити транзакцію

У деяких додатках повідомлення про події можуть стати більш приймання замітку лемим методом обробки подій зміни даних

Додаткова Тригери можуть бути написані з використанням CLR і будь-якої мови семейст-інформація ва NEy Більш докладно це питання буде розглянуто в главі 27

Створення тригерів

Тригери створюються і модифікуються за допомогою стандартних команд мови DDL CREATE, ALTER і DROP наступним чином:

CREATE TRIGGER імя_тріггера ON імя_таблиці AFTER Insert, Update Delete AS

программний_код_тріггера

До виходу версії SQL Server 2000 в SQL Server існували тільки тригери AFTER Оскільки не було гострої необхідності розділяти тригери INSTEAD OF і AFTER, був збережений старий синтаксис FOR INSERT, UPDATE або DELETE Для забезпечення працездатності старих тригерів AFTER вони можуть створюватися з використанням ключового слова FOR замість AFTER

Подібно збереженим процедурам, тригери можуть створюватися з шифруванням Однак це шифрування також легко зламати

Незважаючи на те що я наполегливо рекомендую створювати і змінювати тригери за допомогою сценаріїв і управління версіями, ви можете переглядати і редагувати тригери у вікні Object Explorer утиліти Management Studio (рис 231)

Рис 231 У вікні Object Explorer перераховані всі тригери кожної таблиці За допомогою контекстного меню користувач може редагувати ці тригери

Тригери AFTER

Таблиця може мати кілька тригерів AFTER для кожного з трьох своїх подій (вставка, оновлення і видалення) Тригери AFTER застосовні тільки до таблиць

Традиційний тригер є тригером AFTER, який запускається після виконання транзакції, але до її підтвердження Ці тригери можуть стати в нагоді для таких операцій:

І складна перевірка даних

Про підтримка складних правил бізнес-логіки

Я запис журналу аудиту даних

■ обслуговування змінених стовпців даних

■ реалізація користувальницьких перевірок цілісності даних і каскадних вилучень

Використовуйте тригери after у транзакціях, які з найбільшою ймовірністю будуть успішними, так як ці тригери виконуються після завершення операції, але до її підтвердження З цієї причини тригери after ідеально підходять для перевірки даних і підтримки складних правил

Коли вивчається будь-якої мова программірорванія, зазвичай перша написаною програмою є введення на екран традиційної фрази Hello, world. При цьому від вас більше нічого не потрібно, окрім як скомпілювати програму і переконатися, що ця фраза дійсно відображається на екрані Проводячи аналогію, наступний тригер AFTER при виконан-

USE Family

CREATE TRIGGER TriggerOne ON Person AFTER Insert AS

PRINT After Trigger;

Для тестування даного тригера виконаємо операцію вставки рядки:

INSERT Person(PersonID, LastName, FirstName, Gender)

VALUES (50, Ebob, Bill,M)

Отримаємо наступний результат:

After Trigger (1 row(s) affected)

Як ми бачимо, інструкція вставки була виконана, і тригер вивів на екран закладене в нього повідомлення

Тригери INSTEAD OF

Тригер INSTEAD OF замінює транзакцію (тобто виконується замість неї) Це подібно до того, як якби тригер автоматично відкотив транзакцію, для якої був створений

Кожна таблиця обмежена можливістю використання тільки одного тригера підстановки для кожного зі своїх подій Водночас тригери INSTEAD OF можуть застосовуватися до уявлень точно так само, як до таблиць

Не плутайте тригери INSTEAD OF з тригерами BEFORE або подіями BEFORE UPDATE Це не одне і те ж Тригер BEFORE, якби такий існував в SQL Server, не перетинався б із транзакцією, якби в самому триггере не була передбачена інструкція відкату

Тригери INSTEAD OF особливо корисні, коли заздалегідь відомо, що інструкція DML, запустивши тригер, майже напевно буде скасована, а замість неї повинна бути реалізована деяка логіка Наведемо приклади

■ Інструкція DML намагається оновити неоновлювані уявлення У цьому випадку тригер INSTEAD OF оновлює таблиці, на яких побудовано дане подання

■ Інструкція DML намагається безпосередньо оновити таблицю складських залишків, що неприпустимо У цьому випадку тригер INSTEAD OF оновлює таблицю складських операцій

■ Інструкція DML намагається видалити рядок, а тригер INSTEAD OF замість цього переміщує цю рядок в архівну таблицю

У наступному прикладі ми створимо і протестуємо тригер заміни операції вставки:

CREATE TRIGGER TriggerTwo ON Person INSTEAD OF Insert AS

PRINT Instead of Trigger go

INSERT Person(PersonID, LastName, FirstName, Gender)

VALUES (51, Ebob, ‘,M)

Буде отримано наступний результат:

Instead of Trigger (1 row(s) affected)

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

SELECT LastName FROM Person WHERE PersonID =51

LastName

(0 row(s) affected)

У даному випадку інструкція INSERT відпрацювала так, начебто одна рядок була оброблена, хоча ефект цієї інструкції був блокований тригером INSTEAD OF Замість вставки рядка була виконана операція виведення на екран закладеної в тригер фрази Зауважимо, що створений раніше тригер AFTER залишився в силі, проте його повідомлення не було виведено на екран

Обмеження тригерів

Зважаючи самої природи тригерів (програмний код, прикріплений до таблиці) вони мають кілька обмежень Наступні інструкції неприпустимі в тригерах:

■ CREATE, ALTER і DROP (створення, зміна та видалення таблиці)

■ RECONFIGURE (зміна конфігурації)

■ RESTORE (відновлення бази даних або журналу)

■ DISK RESIZE (зміна розмірів дискового простору)

■ DISK INIТ (ініціалізація диска)

Відключення тригерів

Інструкція DML не може обійти тригер, однак системний адміністратор може його тимчасово відключити Це краще комбінації операцій видалення та відтворення тригера, особливо якщо той виконує завдання модифікації даних

Для тимчасового відключення тригера використовуйте інструкцію DDL ALTER TABLE з параметром DISABLE TRIGGER (для його включення виконується та ж інструкція, але з параметром ENABLE TRIGGER)

ALTER TABLE імя_та бліци DISABLE TRIGGER імя_тріггера ALTER TABLE імя_ та бліци ENABLE TRIGGER імя_тріггера

У наступному прикладі відключається тригер INSTEAD OF з назвою TriggerOne таблиці Person:

ALTER TABLE Person

DISABLE TRIGGER TriggerOne

Для перегляду стану тригера (включений він або відключений) використовується функція objectproperty (), якою передається ідентифікатор тригера і параметр Ехес IsTriggerDisabled:

SELECT OBJECTPROPERTY(

OBJECT_ID(TriggerOne)# 1ExecIsTriggerDisabled)

Створення списку тригерів

Так як тригери приховані в структурі таблиці, для створення списку тригерів, існуючих в базі даних, можна використовувати наступний запит У наведеному прикладі також перевіряється таблиця sysobj ect на предмет обєктів з типом tr, після чого ця таблиця обєд-

няется з таблицею Trigger для можливості вилучення імені таблиці У наведеному нижче прикладі для виклику функції ob j ectproperty () в кожному рядку використаний корельований подзапрос, що перетворює числове стан тригера в словесний опис

SELECT substring(S2Name,1,30) as [Table], substring(SName, 1,30) as [Trigger],

CASE (SELECT — Correlated subquery

OBJECTPROPERTY(OBJECT_ID(SName),

‘ExecIsTriggerDisabled1))

WHEN 0 THEN Включено

WHEN 1 THEN Відключений

END AS Status FROM Sysobjects S JOIN Sysobjects S2

ON Sparent_obj = S2ID WHERE SType = TR

ORDER BY [Table], [Trigger]

Буде отримано наступний результат:

Table           Trigger                                       Status

Person Person_Parents Включений

Person TriggerOne Відключений

Person TriggerTwo Включений

Тригери і безпека

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

Програмний код в тригері виконується, припускаючи дозволу власника таблиці, до якої прикріплений тригер

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*