Цель работы– изучение назначения и типов триггеров, условий их активации, синтаксиса и семантики команд языка 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’.