Триггеры – это хранимые процедуры специального вида, которые автоматически выполняются при изменении таблицы с помощью операторов INSERT, UPDATE и DELETE. Триггер создается для определенной таблицы, но может использовать данные других таблиц и объекты других баз данных.
Существует 3 типа триггеров: INSERT, UPDATE и DELETE. Правила работы с триггерами следующие:
· триггеры запускаются только после выполнения вызвавшего их оператора;
· если при выполнении оператора возникает нарушение какого-либо ограничения или другая ошибка, триггер не срабатывает (даже не начинает выполняться);
· триггер и вызвавший его оператор образует транзакцию. Если нужно из триггера отменить вызвавшую его операцию, следует выполнить откат транзакции ROLLBACK;
· триггер срабатывает один раз для каждого оператора, независимо от количества изменяемых им записей.
Краткий формат триггера (более подробно смотрите в MSDN):
CREATE TRIGGER имя_триггера
ON имя_таблицы
FOR INSERT | UPDATE | DELETE
AS
Код_триггера
Рассмотрим элементарный пример: при обновлении таблицы «Сотрудники» печатается сообщение. (Не делайте подобных триггеров в качестве задания для самостоятельной работы!)
CREATE TRIGGER upd_staff
ON k_staff FOR UPDATE
AS
PRINT 'Обновили таблицу Сотрудники'
После создания триггера нужно протестировать его, выполнив команду UPDATE для таблицы Сотрудники.
При добавлении строки в таблицу ее копия помещается во временную таблицу с именем Inserted, при удалении – с именем Deleted. При обновлении старая версия строки помещается во временную таблицу с именем Deleted, новая – с именем Inserted. Эти временные таблицы часто используются в триггерах.
Рассмотрим пример триггера вставки, который вызывается при выполнении команды INSERT в таблице протоколов счетов. При добавлении новой позиции в счете нам нужно заново пересчитать его общую сумму.
CREATE TRIGGER ins_prot
ON k_protokol FOR INSERT
AS
DECLARE @s_new NUMERIC(9,2),
@kolvo NUMERIC(6),
@bill_num NUMERIC(6)
SELECT @kolvo=kolvo FROM Inserted
IF @kolvo>0
BEGIN
SELECT @s_new=p.price_sum,
@bill_num=bill_num
FROM k_price p, Inserted i
WHERE p.price_num=i.price_num
IF @s_new !=0
UPDATE k_bill
SET bill_sum=bill_sum+@s_new*@kolvo
WHERE k_bill.bill_num=@bill_num
END
Для тестирования триггера следует выполнить команду добавления, например:
Выберем информацию о счете №1:
SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1
Получим:
bill_num bill_sum
-----------------
1 1000
Теперь добавим строку в протокол этого счета:
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(5, 1, 1, 5000);
Снова выберем информацию о счете №1:
SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1
Получим:
bill_num bill_sum
-----------------
1 6000
Как видим, сумма счета увеличилась на стоимость выбранного товара.
Рассмотрим пример триггера удаления, который вызывается при выполнении команды DELETE в таблице протоколов счетов. При удалении позиции в счете нам нужно пересчитать его сумму. Здесь возникает следующая проблема – если в команде DELETE было удалено сразу несколько строк, трудно будет их обработать. Поэтому сначала мы выполняем проверку: сколько строк было удалено. Эта информация хранится в глобальной переменной @@ROWCOUNT. Если количество удаленных строк больше 1, выводим сообщение об ошибке и отменяем команду DELETE. В остальном этот триггер похож на предыдущий.
CREATE TRIGGER del_prot
ON k_protokol FOR DELETE
AS
DECLARE @s_old NUMERIC(9,2),
@kolvo NUMERIC(6),
@bill_num NUMERIC(6)
IF @@ROWCOUNT>1
BEGIN
RAISERROR
('Нельзя удалять более 1 строки за раз!', 16, 1)
ROLLBACK TRAN
END
ELSE
BEGIN
SELECT @kolvo=kolvo FROM Deleted
IF @kolvo>0
BEGIN
SELECT @s_old=p.price_sum,
@bill_num=bill_num
FROM k_price p, Deleted d
WHERE p.price_num=d.price_num
IF @s_old !=0
UPDATE k_bill
SET bill_sum=bill_sum-@s_old*@kolvo
WHERE k_bill.bill_num=@bill_num
END
END
Ту же задачу можно решить другим образом, не ограничивая количество удаляемых счетов. Просто пересчитаем суммы для всех счетов. Если в таблице Deleted есть строки протокола для какого-то счета, его сумма будет уменьшена. Этот триггер получится гораздо короче, но он неэффективен, так как обрабатывает все счета.
Обратите внимание, что в команде UPDATE используется связанный подзапрос.
CREATE TRIGGER del_prot
ON k_protokol FOR DELETE
AS
UPDATE k_bill SET bill_sum = bill_sum -
(SELECT SUM(price_sum*kolvo)
FROM Deleted d
WHERE d.bill_num=k_bill.bill_num)
Выполним команду создания триггера. Все нормально, ошибок нет.
Теперь попробуем удалить какую-нибудь строку из протокола счетов. Выдается ошибка
Cannot insert the value NULL into column 'bill_sum'
В чем же дело? Дело в том, что функция SUM вместо ожидаемых числовых значений 0 возвратила NULL-значения для тех счетов, информации о которых нет в таблице Deleted. Чтобы преобразовать ненужные NULL в числовые нули, удобно использовать функцию ISNULL. Она имеет формат
ISNULL(выражение, значение_вместо_NULL)
В том случае, если выражение не равно NULL, функция возвращает выражение. Если оно равно NULL, то значение_вместо_NULL. Триггер примет вид:
CREATE TRIGGER del_prot
ON k_protokol FOR DELETE
AS
UPDATE k_bill SET bill_sum = bill_sum -
ISNULL((SELECT SUM(price_sum*kolvo)
FROM Deleted d
WHERE d.bill_num=k_bill.bill_num),0)
Рассмотрим еще один пример. В таблице платежей мы (на свою голову) установили составной первичный ключ: "номер_счета, номер_платежа", причем номер_платежа должен быть уникальным только в пределах его счета. Т.о., мы не могли для заполнения этого поля использовать свойство IDENTITY (по умолчанию в этом поле мы назначили 0). Попробуем создать триггер для поиска максимального кода платежа по данному счету и формирования нового номера платежа. Все команды в этом триггере вам уже знакомы.
CREATE TRIGGER ins_pay
ON k_payment FOR INSERT
AS
DECLARE @n NUMERIC(6),
@bill NUMERIC(6)
SELECT @bill=bill_num FROM Inserted
SELECT @n=ISNULL(MAX(p.payment_num), 0)
FROM k_payment p, Inserted i
WHERE p.bill_num=i.bill_num
UPDATE k_payment SET payment_num=@n+1
WHERE bill_num=@bill and payment_num=0
Триггеры также удобно использовать для поддержания ссылочной целостности. Мы уже использовали декларативную ссылочную целостность с помощью внешних ключей, но она имеет исключительно запретительный характер. На самом же деле политика ссылочной целостности может быть пяти видов:
· IGNOGE – игнорировать,
· RESTRICT – запрещать,
· CASCADE – обрабатывать каскадным образом,
· SET DEFAULT – назначать значения по умолчанию,
· SET NULL – назначать NULL-значения.
Политика IGNORE означает, что мы не предусматриваем никаких проверок и ограничений.
Политика RESTRICT действует, когда мы применяем ограничения внешних ключей.
При использовании политики CASCADE мы должны предусмотреть собственную программную обработку, т.е. при изменении родительских таблиц вносить изменения в дочерние таблицы программным образом.
Политика SET DEFAULT состоит в том, что при изменении данных в родительских таблицах дочерним таблицам назначаются значения по умолчанию. Например, при удалении отдела мы можем записать его сотрудников в некоторый другой отдел, который мы считаем отделом по умолчанию.
Политика SET NULL похожа на предыдущую политику, только вместо значений по умолчанию мы назначаем NULL-значения.
Рассмотрим следующий пример. Пусть при удалении счета мы хотим удалять все строки его протокола. Пока у нас на этот случай действует внешний ключ, который запрещает удалять счет, для которого есть протокол.
Уберем этот внешний ключ:
ALTER TABLE k_protokol DROP CONSTRAINT fk_protokol_bill_num
Создадим триггер:
CREATE TRIGGER del_bill
ON k_bill FOR DELETE
AS
DELETE FROM k_protokol WHERE bill_num IN
(SELECT bill_num FROM Deleted d)
Протестируем триггер. Распечатаем сначала протокол счета с номером 5.
SELECT * FROM k_protokol WHERE bill_num=5
price_num bill_num kolvo price_sum
--------------------------------------
1 5 1 1000.00
2 5 10 100.00
(2 row(s) affected)
Теперь удалим этот счет.
DELETE FROM k_bill WHERE bill_num=5
Снова распечатаем протокол этого счета.
SELECT * FROM k_protokol WHERE bill_num=5
price_num bill_num kolvo price_sum
--------------------------------------
(0 row(s) affected)
Как видим, строки протокола тоже удалены.
Заметим, что этот триггер удаляет строки из таблицы k_protokol, вызывая тем самым ее собственный триггер. Такие цепочки вызовов триггеров могут быть и более длинными, главное – чтобы триггеры не конфликтовали друг с другом и не зацикливались.
