drop trigger trig_ddl1 on all server

Аналогічним чином необхідно чинити також при перевірці існування і видаленні LOGON тригерів.

*/

if exists(select name from sys.triggers where name = 'trig_ddl1' and parent_class = 0)

Drop trigger trig_ddl1 on database

Go

- Створюємо DDL тригер

Create trigger trig_ddl1

On database

For DROP_TABLE

As

Begin

/*

За допомогою методу value() одержуємо вміст полів ObjectName і SchemaName (великі і маленькі букви різняться) xml даних, що повертаються функцією eventdata(), що при цьому неявно приводиться до типу даних varchar(128). Ці поля зберігають ім'я об'єкта, що видаляється, і його простір імен. Якщо ці елементи збігаються зі значеннями table1 і dbo, відповідно, то робимо відкат транзакції для скасування команди видалення таблиці.

*/

if (eventdata().value('(EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)') = 'table1')

And

(eventdata().value('(EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)') = 'dbo')

ROLLBACK transaction

End

Go

Для події DROP_TABLE, як і для більшості подібних подій, функцією eventdata() вертаються xml дані, що містять наступні елементи(параметри):

Якщо після створення тригера trig_ddl1 виконати наступні SQL команди:

Drop table table1

Go

select * from table1

то в результаті їх роботи буде отримане повідомлення, подібне наступному:

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted.

а також такий результат вибірки даних:

Очевидно, що спроба видалення таблиці з ім'ям table1 до успіху не привела.

Приклад 5.

Створення DDL тригера, що реагує на групу подій DDL_TABLE_EVENTS, що включає події CREATE_TABLE, ALTER_TABLE і DROP_TABLE. При цьому тригер не дозволяє створювати таблиці в поточної БД, а також не дозволяє видаляти й змінювати структуру таблиці table1.

if exists(select name from sys.triggers where name = 'trig_ddl2' and parent_class = 0)

Drop trigger trig_ddl2 on database

Go

Create trigger trig_ddl2

On database

For DDL_TABLE_EVENTS

As

Begin

declare @command varchar(8000),

@word varchar(20)

/*

Одержуємо текст TSQL команди, що активувала тригер, видаляючи в неї початкові незначущі пробіли

*/

set @command = LTRIM

(

eventdata().value('(EVENT_INSTANCE/TSQLCommand)[1]',

'varchar(8000)')

)

/*

Виділяємо з отриманої команди підстроку, починаючи від 1-ого символу, довжина якої дорівнює позиції 1-ого пробілу - 1

*/

set @word = substring(@command, 1, CHARINDEX(' ', @command, 1) - 1)

if (UPPER(@word) = 'CREATE')

Or

(eventdata().value('(EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)') = 'table1')

And

(eventdata().value('(EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)') = 'dbo')

ROLLBACK transaction

End

Go

Після створення тригера спроба виконання кожної з нижчеподаних команд SQL:

Create table table5 (c1 int)

Drop table table1

Alter table table1 add c4 int null

призводить до появи повідомлення, подібного цьому:

Msg 3609, Level 16, State 2, Line 4

The transaction ended in the trigger. The batch has been aborted.

LOGON тригери.

При спрацьовуванні будь-якого LOGON тригера можна, як і у випадку з DDL тригерами, використовувати функцію eventdata(), але єдино можливим при цьому буде подія LOGON, що має наступні елементи(параметри):

Для створення LOGON тригера користувач повинен мати привілей CONTROL SERVER. Всі DDL тригери рівня сервера і LOGON тригери зберігаються в БД master. У випадку неправильного написання LOGON тригера можлива ситуація, коли БУДЬ-ЯКЕ підключення до MS SQL Server 2005 стане неможливим.

Приклад 7.

Створення найпростішого LOGON тригера, що для зазначеного логіна обмежує кількість одночасно встановлених з'єднань із MS SQL Server 2005 до 2 (з тим самим логіном може бути зв'язане декілька користувачів).

if exists(select name from sys.server_triggers where name = 'trig_logon1')

Drop trigger trig_logon1 on all server

Go

Create trigger trig_logon1

On all server

For logon

As

Begin

if (ORIGINAL_LOGIN() = 'new_usr') and

((SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'new_usr') > 1

)

Rollback;

End

Go

У вищерозглянутому прикладі функція ORIGINAL_LOGIN() повертає назву логіна, а системене подання sys.dm_exec_sessions містить записи про всі активні з’єднання із сервером, причому поле is_user_process визначає тип з'єднання: системне - 0, всі інші - 1, а поле original_login_name - назву логіна, під яким з'єднання було встановлене. У випадку перевищення заданого ліміту спроба з'єднання із сервером під логіном new_usr приведе до помилки і, відповідно, неможливості підключення.

Завдання.

1. Написати DML тригер, що виконується замість додавання (INSERT) і зміни (UPDATE) для таблиці, що містить перелік назв статей, що для кожного що додається (змінюваного) рядка перевіряє збіг струтктури, що додається (змінюваного) назви статті (не з огляду на пробіли, що розділяють слова) з іншими назвами. У випадку збігу додавання (зміна) не відбувається і генерується повідомлення про помилку, рівень серйозності (severity) якої допускає її обробку блоком TRY .. CATCH, за допомогою команди RAISERROR. Аналогічний тригер написати для таблиці, що містить перелік назв тез доповідей на конференціях.

2. Написати DML тригер, що виконується замість додавання (INSERT) і зміни (UPDATE) для таблиці, що містить інформацію про особистість (студента), що при додаванні (зміні) чергового рядка перевіряє збіг П.І.Б. і дати народження з іншими особистостями. У випадку їхнього збігу генерується повідомлення про помилку, рівень серйозності (severity) якої допускає її обробку блоком TRY .. CATCH, за допомогою команди RAISERROR.

3. Написати DML тригер, що виконується замість видалення (DELETE) для таблиці, що містить інформацію про студентів, що не дозволяє видаляти рядки цієї таблиці. При спробі видалення генерується повідомлення про помилку, рівень серйозності (severity) якої допускає її обробку блоком TRY .. CATCH, за допомогою команди RAISERROR.

4. Написати DML тригер, що виконується після команд додавання (INSERT) і зміни (UPDATE) для таблиці, що містить інформацію про успішність студентів. Цей тригер після додавання (зміни) чергового рядка таблиці повинен перевіряти оцінку і, якщо вона незадовільна, копіювати зроблений запис у спеціальну таблицю (яка повинна існувати), що містить інформацію про боржників. Якщо незадовільна оцінка з якого-небудь предмету змінюється на позитивну, то відповідний запис із таблиці «боржників» видаляється.

5. Написати DML тригер, що виконується після команд додавання (INSERT) і зміни (UPDATE) для таблиці, що містить інформацію про студентів-«боржників». Цей тригер після додавання (зміни) чергового рядка повинен перевірити кількість негативних оцінок («боргів») для даного студента і, якщо вона стає рівною 3, записати інформацію про студента і його «борги» у таблицю, що містить інформацію про студентів-«боржників», які підлягають відрахуванню (повинна існувати, структуру розробити самостійно). В останньому випадку тригер повинен сгенерувати повідомлення про помилку, рівень серйозності (severity) якої допускає її обробку блоком TRY .. CATCH, за допомогою команди RAISERROR.

6. Написати DML тригер, що виконується після команди видалення (DELETE) для таблиці, що містить інформацію про студентів-«боржників». Цей тригер повинен перевірити наявність інформації про студента, що перездав «борг», у таблиці, що містить інформацію про студентів-«боржниківх», які підлягають відрахуванню і, у випадку виявлення, видалити її.

7. Написати DDL тригер, що при спробі видалення кожної з таблиць, створених при виконанні лабораторних робіт №3 і №5 робить відкат транзакції.

8. Написати DDL тригер, що при спробі видалення обмеження PRIMARY KEY для кожної з таблиць, створених при виконанні лабораторних робіт №3 і №5 робить відкат транзакції.

9. Написати DDL тригер, що при спробі видалення обмеження FOREIGN KEY для таблиць, що його мають, створених при виконанні лабораторних робіт №3 і №5 робить відкат транзакції.

10.