Программирование триггера

При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным:

команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;
команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT; она возвращает количество строк, обработанных последней командой. Следует подчеркнуть, что триггер запускается не при попытке изменить конкретную строку, а в момент выполнения команды изменения. Одна такая команда воздействует на множество строк, поэтому триггер должен обрабатывать все эти строки.

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

Триггер выполняется как неявно определенная транзакция, поэтому внутри триггера допускается применение команд управления транзакциями. В частности, при обнаружении нарушения ограничений целостности для прерывания выполнения триггера и отмены всех изменений, которые пытался выполнить пользователь, необходимо использовать команду ROLLBACK TRANSACTION.

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE, вызвавших выполнение триггера, можно использовать функцию COLUMNS_UPDATED(). Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение "1", то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

Для удаления триггера используется команда

DROP TRIGGER {имя_триггера} [,...n]

Приведем примеры использования триггеров.

Пример 14.1. Использование триггера для реализации ограничений на значение. В добавляемой в таблицу Сделка записи количество проданного товара должно быть не больше, чем его остаток из таблицы Склад.

Команда вставки записи в таблицу Сделка может быть, например, такой:

INSERT INTO Сделка VALUES (3,1,-299,'01/08/2002')

Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице Склад величина остатка товара оказалась меньше продаваемого количества товара с введенным кодом (в примере код товара=3). Во вставляемой записи количество товара указывается со знаком "+", если товар поставляется, и со знаком "-", если он продается. Представленный триггер настроен на обработку только одной добавляемой записи.

CREATE TRIGGER Триггер_insON Сделка FOR INSERTASIF @@ROWCOUNT=1BEGIN IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT 'Отмена поставки: товара на складе нет' ENDEND

Пример 14.1. Использование триггера для реализации ограничений на значение. (html, txt)

Пример 14.2. Использования триггера для сбора статистических данных.

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

INSERT INTO Сделка VALUES (3,1,200,'01/08/2002')

поставляется товар с кодом 3 от клиента с кодом 1 в количестве 200 единиц.

При продаже или получении товара необходимо соответствующим образом изменить количество его складского запаса. Если товара на складе еще нет, необходимо добавить соответствующую запись в таблицу Склад. Триггер обрабатывает только одну добавляемую строку.

ALTER TRIGGER Триггер_insON Сделка FOR INSERTASDECLARE @x INT, @y INTIF @@ROWCOUNT=1--в таблицу Сделка добавляется запись --о поставке товараBEGIN--количество проданного товара должно быть не--меньше, чем его остаток из таблицы СкладIF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT 'откат товара нет ' END--если записи о поставленном товаре еще нет,--добавляется соответствующая запись--в таблицу Склад IF NOT EXISTS ( SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара ) INSERT INTO Склад (КодТовара,Остаток) ELSE--если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара из добавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара--и производится изменения количества товара в--таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y ENDEND

Пример 14.2. Использования триггера для сбора статистических данных. (html, txt)

Пример 14.3. Создать триггер для обработки операции удаления записи из таблицы Сделка, например, такой команды:

DELETE FROM Сделка WHERE КодСделки=4

Для товара, код которого указан при удалении записи, необходимо откорректировать его остаток на складе. Триггер обрабатывает только одну удаляемую запись.

CREATE TRIGGER Триггер_delON Сделка FOR DELETEASIF @@ROWCOUNT=1 -- удалена одна записьBEGIN DECLARE @y INT,@x INT--определяется код и количество товара из --удаленной из таблицы Склад записи SELECT @y=КодТовара, @x=Количество FROM deleted--в таблице Склад корректируется количество --товара UPDATE Склад SET Остаток=Остаток-@x WHERE КодТовара=@yEND

Пример 14.3. Триггер для обработки операции удаления записи из таблицы (html, txt)

Пример 14.4. Создать триггер для обработки операции изменения записи в таблице Сделка, например, такой командой:

UPDATE Сделка SET количество=количество-10WHERE КодТовара=3

во всех сделках с товаром, имеющим код, равный 3, уменьшить количество товара на 10 единиц.

Указанная команда может привести к изменению сразу нескольких записей в таблице Сделка. Поэтому покажем, как создать триггер, обрабатывающий не одну запись. Для каждой измененной записи необходимо для старого (до изменения) кода товара уменьшить остаток товара на складе на величину старого (до изменения) количества товара и для нового (после изменения) кода товара увеличить его остаток на складе на величину нового (после изменения) значения. Чтобы обработать все измененные записи, введем курсоры, в которых сохраним все старые (из таблицы deleted) и все новые значения (из таблицы inserted).

CREATE TRIGGER Триггер_updON Сделка FOR UPDATEASDECLARE @x INT, @x_old INT, @y INT, @y_old INT-- курсор с новыми значениямиDECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted-- курсор со старыми значениямиDECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deletedOPEN CUR1OPEN CUR2-- перемещаемся параллельно по обоим курсорам FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN--для старого кода товара уменьшается его--количество на складе UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old--для нового кода товара, если такого товара--еще нет на складе, вводится новая запись IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE--иначе для нового кода товара увеличивается--его количество на складе UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_oldENDCLOSE CUR1CLOSE CUR2DEALLOCATE CUR1DEALLOCATE CUR2

Пример 14.4. триггер для обработки операции изменения записи в таблице (html, txt)

В рассмотренном триггере отсутствует сравнение количества товара при изменении записи о сделке с его остатком на складе.

Пример 14.5. Исправим этот недостаток. Для генерирования сообщения об ошибке используем в теле триггера команду MS SQL Server RAISERROR, аргументами которой являются текст сообщения, уровень серьезности и статус ошибки.

ALTER TRIGGER Триггер_updON Сделка FOR UPDATEASDECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INTDECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM insertedDECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deletedOPEN CUR1OPEN CUR2 FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE кодтовара=@x IF @o<-@y BEGIN RAISERROR('откат',16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_oldENDCLOSE CUR1CLOSE CUR2DEALLOCATE CUR1DEALLOCATE CUR2

Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице (html, txt)

Пример 14.6. В примере 14.5 происходит отмена всех изменений при невозможности реализовать хотя бы одно из них. Создадим триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

В этом случае триггер выполняется не после изменения записей, а вместо команды изменения.

ALTER TRIGGER Триггер_updON Сделка INSTEAD OF UPDATEASDECLARE @k INT, @k_old INTDECLARE @x INT, @x_old INT, @y INTDECLARE @y_old INT ,@o INTDECLARE CUR1 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM insertedDECLARE CUR2 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM deletedOPEN CUR1OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE КодТовара=@x IF @o>=-@y BEGIN RAISERROR('изменение',16,10) UPDATE Сделка SET количество=@y, КодТовара=@x WHERE КодСделки=@k UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара, Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x END ELSE RAISERROR('запись не изменена',16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_oldENDCLOSE CUR1CLOSE CUR2DEALLOCATE CUR1DEALLOCATE CUR2

Лекция: Триггеры в рекурсивных структурах

<26 27 282930 31 32 >

Дата добавления: 2015-08-26; просмотров: 1516;