Тригери у Transact SQL

Для аналізу синтаксису команди CREATE TRIGGER у Transact SQL скористаємось Books Online (Пуск\Microsoft SQL Server\Books Online).

 

 

Рис.14.13. Як знайти опис команди CREATE TRIGGER у BOOKS ONLINE

 

Наведемо визначення синтаксису команди CREATE TRIGGER у Transact SQL.

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ ,] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE (column )
[ { AND | OR } UPDATE (column )]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ){ bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [...n ]
}
}

Рис.14.14. Transact-SQL cинтаксис команди CREATE TRIGGER у BOOKS ONLINE

 

Рядки	Що вони містять
CREATE TRIGGER trigger_name	Заголовок, назва тригера
ON { table | view }	Ім’я таблиці або візії
[ WITH ENCRYPTION ]	Тригер зачиняється від інших користувачів і не реплікується
{ {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ ,] [ UPDATE ] }	FOR еквівалентно AFTER, залишено для сумісності. Помилково пропущено слово [DELETE]. deleted and inserted – таблиці попередніх і нових даних.
[ WITH APPEND ]	Для сумісності з версією 6.5.
[ NOT FOR REPLICATION ]	Тригер не виконується при реплікації
[ { IF UPDATE (column ) [ { AND | OR } UPDATE (column )] [ ...n ]	Перевірка, чи оновлювались колонки column
| IF ( COLUMNS_UPDATED ( ){ bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ]	Визначення, чи оновлювався набір колонок, за допомогою бітової маски
sql_statement [...n ]	Умови і дії тригера як послідовність команд Transact-SQL deleted and inserted – таблиці попередніх і нових даних.

 

Усього опис з прикладами займає12 великих екранних сторінок.

 

Приклад 14.10. Використання тригера для нагадування за допомогою Email

USE pubs1) IF EXISTS (SELECT name FROM sysobjects2) WHERE name = 'reminder' AND type = 'TR')3) DROP TRIGGER reminder4) GO5) CREATE TRIGGER reminder6) ON titles7) FOR INSERT, UPDATE, DELETE 8) AS9) EXEC master..xp_sendmail 'MaryM', 10) 'Don''t forget to print a report for the distributors.'11) GO

 

Рис.14.15. Використання тригера для нагадування за допомогою Email

 

У цьому прикладі рядки 1-4 забезпечують видалення попередньої версії тригера. Будь-які зміни відношення titles (рядки 6,7) тягнуть відсилання Email адресату 'MaryM' з нагадуванням про необхідність роздрукувати звіт.

 

xp_sendmail відсилає вказаному адресату листа та приєднує результати виконання запиту. Опис xp_sendmail займає 4 великих екранних сторінки.

 

Команда GO (рядки 4, 11) формує пакет (від GO до GO). Пакет виконується по одному плану виконання, хоча не є транзакцією.

 

Приклад 14.11. ([8], стор.293). Нехай відношення Books містить перелік книг, а відношення BookOrders фіксує факти продажу книг. Якщо книга продана, у відношення BookOrders додається кортеж, і тоді у відношення Books в атрибуті sold треба поставити 1.

 

CREATE TRIGGER dbo.update_book_status

ON dbo.bookOrders

AFTER INSERT

AS

UPDATE Books

SET sold=1

WHERE titleId=

(SELECT bo.titleId

FROM bookOrders bo INNER JOIN inserted i

ON bo.orderId=i.orderId)

 

Рис.14.16. Приклад тригера для оновлення іншого відношення в разі вставки.

 

В передостанньому рядку використане відношення inserted, по якому ми визначаємо Id щойно проданої книги, та оновлюємо відповідний кортеж у Books. Напряму звернутись до inserted не можна, треба його з’єднати з іншим відношенням.