Создание, программирование и управление триггерами

 

 

Цель работы– изучение назначения и типов триггеров, условий их активации, синтаксиса и семантики команд языка Transact – SQL для их создания, модификации, переименования, программирования и удаления, а также приобретение навыков их проектирования, кодирования и отладки с применением хранимых процедур для получения информации о триггерах базы данных.

 

Триггер SQL Server 2000 – это специальный тип хранимых процеду, которые запускаются сервером автоматически при выполнении тех или иных действий с данными таблицы. Триггеры рвзличаются по типу команд, на которые они реагируют:

INSERT TRIGGER – запускаются при попытке вставить данные с помощью команды

INSERT;

UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды

UPDATE;

authsmall TRIGGER – запускаются при попытке удаления данных с помощью команды

DELETE.

Парамнтры FOR, AFTER и INSTEAD OF, указываемые при создании триггера,

определяют его поведение следующим образом:

FOR – запуск триггера при выполнении заданной в этом списке команды; AFTER – запуск триггера после успешного выполнеиния команд списка; INSTEAD OF – триггеры вызывается вместо выполнения команд списка.

Можно определить несколько AFTER – триггеров для каждой операции INSERT, UPDATE и DELETE. По умолчаню все триггеры являются AFTER – триггерами. Триггеры нельзясоздавать для временных или системных таблиц. Команда создания триггера должна быть первой в пакете и применяться только к одной таблице. Ее формат следующий:

 

CREATE TRIGGER Имя триггера

ON {Имя таблицы\Имя представления}

[WITH ENCRYPTION] -- шифрование кода триггера;

{ {{FOR\AFTER\INSTEAD OF}

{[DELETE] [,] [INSERT] [,] [UPDATE]}

[WITE APPEND] -- только для версий 6.5 и ниже; [NOT FOR REPLICATION] -- не для репликации; AS sql_statement […n] -- тело триггера;

}

|

{{FOR\AFTER\INSTEAD OF}

{[INSERT] [,] [UPDATE]}

[WITE APPEND] -- только для версий 6.5 и ниже; [NOT FOR REPLICATION] -- не длярепликации;

AS {IF UPDATE (column) -- при изменении столбца; [{AND\OR}UPDATE (column) […n]]}-- тоже;

|

IF (COLUMNS_UPDATED() {bitwise_operator} Update_bitmask)

{comparison_operator}column_bitmask […n]

}

sql_statement […n] -- тело триггера.

}

}

 

Вторая альтернатива команды {IF UPDATE…} используется для детального анализа изменений содержимого колонок с помощью специальных функций, битовых масок, операторов побитовой обработки, оператор сравнения и логических операторов.

Команда ALTER TRIGGER позволяет изменить параметры и тело триггера. С помощью команды DROP TRIGGER можно удалть любой триггер базы данных. Переименовать триггер можно системной хранимой процедурой sp_rename, а получить информацию о триггере можно при помощи системных хранимых процедур sp_helptext и sp_helptrigger.

Внутри триггера допускается использование любых команд языка Transact – SQL с некоторыми ограничениями. Также допускается и вызов хранимых процедур, включая системные.

 

 

Задание 1.Создать таблицу authsmall из таблицы authors базы данных Pubs и для новой таблицы запрограммировать триггер auth_del, который будет выводить информацию о попытках удаления и количестве удаляемых строк, выполнив действия:

 

1. Создание таблицы authsmall c колонками au_id, au_fname, au_lname, phone и копирование в нее данных из таблицы authors:

 

SELECT au_id, au_fname, au_lname, phone

INTO authsmoll

FROM authors

PRINT ‘Содержимое таблицы authsmoll:’ SELECT * FROM authsmall

2. Создание и программирование триггера: CREATE TRIGGER auth_del

ON authsmall FOR DELETE AS

PRINT ‘Попытка удаления’ + STR (@@ POWCOUNT)+

‘строк в таблице authsmall’

PRINT ‘Пользователь’ + CURRENT_USER IF CURRENT_USER <> ‘dbo’

BEGIN

PRINT ‘Удаление запрещено’ ROLLBACK TRANSACTION END

ELSE

PRINT ‘Удаление разрешено’

 

3. Тестирование триггера :

 

DELETE FROM authsmall WHERE au_fname = ‘Johnson’ DELETE FROM authsmall WHERE 2*2=5

 

Задание 2.Создать триггер auth_upd для таблицы authsmall, построенный в первом задании, который будет разрешать изменение столбца au_id этой таблицы всем, кроме владельца dbo, выполнив следующие действия:

1. Создание и программирование триггера: CREATE TRIGGER auth_upd

ON authsmall

FOR UPDATE

AS

SET NOCOUNT ON -- не сообщать о завершении команд; PRINT ‘Попытка изменения данных в таблице authsmall’ IF (COLUMNS_UPDATE () &1)! = 0 -- 1-й столбец;

PRINT ‘Изменение столбца au_id’

IF (COLUMNS_UPDATE () &2)! = 0 -- 2-й столбец; PRINT ‘Изменение столбца au_fname’

IF (COLUMNS_UPDATE () &4)! = 0 -- 3-й столбец; PRINT ‘Изменение столбца au_lname’

IF UPDATE (Phone)

PRINT ‘Изменение столбца phone’ IF ((CURRENT_USER = ‘dbo’) AND

(COLUMNS_UPDATED()&1)! = 0 -- 1-ый стлбец; BEGIN

PRINT ‘Пользователь dbo не может изменять’ + ‘идентификационный номер автора’

ROLLBACK TRANSACTION END

 

2. Тестирование триггера:

 

UPDATED authsmall SET phone =‘415 986 - 7020’, au_fname = ‘John’ WHERE au_lname = ‘Green’

UPDATED authsmall SET phone =‘913 843 - 7302’, au_id = ‘748-126859’ WHERE au_lname = ‘Smith’

 

Задание 3.Создать триггер для команд INSERT и UPDATE, запрещающий производить изменения для автора Billy Geitsi, выполнив действия:

1. Создание и программирование триггера: CREATE TRIGGER auth_ ins_upd ON authsmall

FOR INSERT, UPDATE

AS

IF EXISTS (SELECT * FROM authsmall -- inserted; WHERE au_lname = ‘Geitsi’ -- фамилия;

au_fname = ‘Billy’) -- имя;

BEGIN

PRINT ‘Недопустимо написание кнги’+

‘автором Billy Geitsi’ ROLLBACK TRANSACTION

END

 

2. Тестирование триггера:

UPDATE authsmall SET au_lname = ‘Geitsi’,

au_fname = ‘Billy’ WHERE au_lname = ‘Smith’.