CREATE TABLE table1 (c1 int, c2 int, c3 int primary key identity)

і містить дані, наведені в табл. 1.

Табл. 1. Уміст таблиці table1

Припускаємо, що з поточним користувачем асоційований простір імен dbo.

DML тригери.

У всіх прикладах передбачається, що з'єднання із СУБД було зроблено під таким логіном (користувачем), для якого простором імен є dbo.

Приклад 1.

Створення найпростішого AFTER тригера, що спрацьовує при виконанні команди DELETE і у випадку видалення одного з рядків таблиці, що містять у стовпці c1 значення 1, видаляє всі такі рядки в таблиці. Крім цього виникає проблема, пов'язана з перевіркою того, чи існує такий тригер у даному просторі імен.

/*

Перед створенням тригера необхідно переконатися, що такого тригера у використовуваному просторі імен немає. Для цього необхідно зробити відповідний запит до системної таблиці sysobjects, у якому необхідно вибрати рядок, що містить інформацію про об'єкт із ім'ям trig1, тип якого TR (тригер). Крім цього поле uid (номер схеми) повинне відповідати номеру потрібного простору імен. Цей номер можна одержати шляхом вибірки із системного подання sys.schemas поля schema_id у рядка, що містить у полі name відповідну назву простору імен.

*/

if exists(select * from sysobjects where name = 'trig1' and

uid = (select schema_id from sys.schemas where name = 'dbo') and type = 'TR')

Drop trigger trig1

Go

/*

В MS SQL Server 2005 системна таблиця sysobjects відсутня. Вона, як і деякі інші системні таблиці, включена як подання для сумісності з SQL скриптами, написаними для MS SQL Server 2000, і відображається (імена і кількість стовпців не збігаються) у системне подання sys.objects MS SQL Server 2005, яким і рекомендується користуватися. Отже, вищенаведений фрагмент коду рекомендується написати так:

if exists(select * from sys.objects where name = 'trig1' and

schema_id = (select schema_id from sys.schemas where name = 'dbo')

and type = 'TR')

Drop trigger trig1

Go

*/

Create trigger trig1

On table1

After delete

Not for replication

As

Begin

if exists(select c1 from deleted where c1 = 1)

Begin

delete from table1 where c1 = 1

End

End

Go

Після виконання команди SQL:

delete from table1 where c2 = 2

тригер буде активований і в результаті його спрацьовування таблиця table1 матиме такий вигляд:

Цей тригер буде викликати себе рекурсивно, тому що в його тілі присутня команда DELETE, що буде викликана у випадку видалення з таблиці table1 рядків, що містять значення 1 у стовпці c1. Якщо з якої-небудь причини неможливо (незручно) заборонити рекурсивні виклики для тригерів (у явному або непрямому виді), а кількість цих викликів необхідно обмежити, то у вищерозглянутому випадку можна було б це зробити таким чином:

Create trigger trig1

On table1

After delete

Not for replication

As

Begin

Declare

@var int

set @var = (select [object_id] from sys.objects where name = 'trig1' and TYPE = 'TR'

and schema_id = (select schema_id from sys.schemas where name = 'dbo')

)

if (TRIGGER_NESTLEVEL(@var, 'AFTER', 'DML') = 1)

Begin

if exists(select c1 from deleted where c1 = 1)

Begin

delete from table1 where c1 = 1

End

End

End

Go

У цьому випадку при створенні тригера в його тілі визначається ідентифікатор тригера, що одержується на підставі його імені із системного подання sys.objects. Після цього використовується функція TRIGGER_NESTLEVEL, що у якості 1-ого аргументу одержує ідентифікатор тригера, що цікавить нас, у якості 2-ого аргумента одержує тип спрацьовування тригера ('AFTER' або 'IOT' для INSTEAD OF тригерів), а в якості 3-го аргумента - тип тригера ('DML' або 'DDL'). У результаті своєї роботи ця функція повертає кількість рекурсивних викликів (викликів, поміщених у стеці) для обраного тригера (тригерів). Якщо кількість спрацьовувань = 1, то виконуються заплановані в тригері дії. У такий спосіб максимальна кількість рекурсивних спрацьовувань тригера не перевищує 2 разів.

При використанні функції TRIGGER_NESTLEVEL, треба враховувати, що

1. Всі її аргументи є необов'язковими. Якщо всі аргументи відсутні, то обчислюється кількість рекурсивних викликів для всіх видів тригерів. Якщо який-небудь із аргументів відсутній, то маються на увазі всі тригери зазначеного виду (виду спрацьовування) або типу.

2. Якщо зазначено 2-ий аргумент, то варто вказувати і 3-ій аргумент.

Приклад 2.

Створення найпростішого INSTEAD OF тригера, що спрацьовує при виконанні команди DELETE і повторює дії, які ця команда виконувала над даними таблиці.

if exists (select * from sys.objects where name = 'trig2' and

schema_id = (select schema_id from sys.schemas where name = 'dbo')

and type = 'TR')

Drop trigger trig2

Go

Create trigger trig2

On table1

Instead of delete

Not for replication

As

Begin

Declare

@var int

set @var = (select [object_id] from sys.objects where

name = 'trig2'and TYPE = 'TR'

and schema_id = (select schema_id from sys.schemas

where name = 'dbo'

)

)

if (TRIGGER_NESTLEVEL(@var, 'IOT', 'DML') = 1)

Begin

delete from table1 from deleted d where table1.c3 = d.c3

End

End

Go

Оскільки тригер INSTEAD OF спрацьовує замість команди DELETE, то дані з таблиці table1 віддалятися не будуть. Для того щоб їх видалити (видаляються дані, що, зберігаються в спеціальній таблиці deleted), необхідно повторити цю дію в тілі тригера. Як і в минулому прикладі, максимальна кількість рекурсивних викликів цього тригера = 2.

У результаті виконання команди:

delete table1 where c1 = 2

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

Приклад 3.

Створення найпростішого INSTEAD OF тригера, що спрацьовує при виконанні всіх трьох команд (INSERT, UPDATE, DELETE) і, визначаючи тип команди, повторює дії, які команда повинна була виконати над даними таблиці.

if exists(select * from sys.objects where name = 'trig3' and schema_id = (select schema_id from sys.schemas where name = 'dbo') and type = 'TR')

Drop trigger trig3

Go

Create trigger trig3

On table1