Читайте также:
|
POSITION 0
AS BEGIN
IF (NEW.“КодСотрудника” IS NULL)
THEN NEW.“КодСотрудника” = GEN_ID (GEN_STUFF_ID, 1);
END
2) Имеются две связанные таблицы Работники(ID, ФИО, СуммаЗаказов) и Заказы(Номер, Дата, Сумма, ID_Работника (FK)). Требуется при добавлении нового заказа или изменении поля Сумма заказа автоматически обновлять СуммуЗаказов работника, курирующего этот заказ. Будет два триггера (на INSERT и UPDATE) таблицы Заказы, срабатывающих безразлично когда – до или после – вставки. Логичнее после.
CREATE TRIGGER ORDER_AI_0 FOR “Заказы”
AFTER INSERT
POSITION 0
AS BEGIN
UPDATE “Работники”
SET “СуммаЗаказов” = “СуммаЗаказов” + NEW. “Сумма”
WHERE “ID” = NEW. “ID_Работника”
END
CREATE TRIGGER ORDER_AU_0 FOR “Заказы”
AFTER UPDATE
POSITION 0
AS BEGIN
UPDATE “Работники”
SET “СуммаЗаказов” = “СуммаЗаказов” + (NEW. “Сумма” - OLD. “Сумма”)
WHERE “ID” = NEW. “ID_Работника”
END
Есть два подхода к решению этой задачи. Можно создать триггеры, как описано выше, и добавлять заказ обычным запросом INSERT, а менять сумму обычным запросом UPDATE. Другой вариант – триггеры не использовать, а добавление заказа и изменение суммы сделать хранимыми процедурами, и обновлять таблицу Работники именно там. Второй вариант предпочтительнее из-за гонок триггеров. Где можно, триггеры лучше не использовать.
В Microsoft SQL Server 2000 существует два типа триггеров: обычные, вызываемые после операции (AFTER), и замещающие, вызываемые вместо операции (INSTEAD OF). Последние используются для обновления представлений (view – см. ЛР №3). Операции (insert, update, delete) на которые вызывается триггер, могут комбинироваться в любом сочетании (в отличие от Firebird, где триггер привязывался только к одной операции).
Триггер создается инструкцией
CREATE TRIGGER ИмяТриггера
ON ИмяТаблицы
WITH ENCRYPTION /* необязательный параметр, вызывается,
если тело триггера нужно зашифровать */
{FOR | INSTEAD OF} { INSERT,UPDATE,DELETE} /*перечисляются операции*/
AS
BEGIN
инструкция SQL;
...
инструкция SQL;
END
Удаляется инструкцией
DROP PROCEDURE ИмяТриггера
Включение/отключение триггеров реализуется оператором ALTER TABLE:
ALTER TABLE Имя_таблицы
{ ENABLE|DISABLE} TRIGGER { Список_имен_триггеров | ALL }
Контекстные переменные в теле триггера представляют собой таблицы с именами INSERTED и DELETED. Столбцы этих таблиц именуются так же, как и столбцы таблицы, к которой привязан триггер. Если одним оператором (например, insert), добавляется группа строк, то триггер FOR INSERT будет вызван один раз после вставки всех строк, и таблица INSERTED будет содержать добавленные строки. Аналогично таблица DELETED содержит удаленные строки. При обновлении строк инструкцией update таблица DELETED содержит старые значения модифицированных строк, а INSERTED – новые значения (эти же значения сейчас сохранены в таблице). Триггеры операций UPDATE и DELETE вызываются даже в том случае, если операция не удалила и не обновила ни одной строки, так как не нашлось строк, соответствующих условию WHERE.
В теле триггеров INSERT и UPDATE можно с помощью особой проверки IF UPDATE(ИмяСтолбца) выявить факт обновления конкретного столбца, что позволяет создавать один универсальный триггер для всех операций. Приведем пример.
Имеются две связанные таблицы Работники(ID, ФИО, СуммаЗаказов) и Заказы(Номер, Дата, Сумма, ID_Работника (FK)). Требуется при добавлении нового заказа автоматически увеличивать СуммуЗаказов соответствующего работника, при удалении заказа автоматически уменьшать, а при изменении поля Сумма заказа автоматически корректировать СуммуЗаказов.
CREATE TRIGGER TR_ORDERS_IUD
ON “ Заказы ”
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @InsertedCount INT;
DECLARE @DeletedCount INT;
SET @InsertedCount = (SELECT COUNT(*) FROM INSERTED);
SET @DeletedCount = (SELECT COUNT(*) FROM DELETED);
IF (@DeletedCount > 0)
/* строки удалили или обновили */
IF (@InsertedCount > 0) AND UPDATE(“Сумма”)
/* было обновление строк, изменился столбец “Сумма” */
UPDATE [Работники]
SET [СуммаЗаказов]=[СуммаЗаказов]+(ZNew.[СуммаОбщ]- ZOld.[СуммаОбщ])
FROM [Работники] R,
(SELECT [ID_Работника], SUM([Сумма]) as [СуммаОбщ]
FROM DELETED
GROUP BY [ID_Работника]) ZOld,
(SELECT [ID_Работника], SUM([Сумма]) as [СуммаОбщ]
FROM INSERTED
GROUP BY [ID_Работника]) ZNew
WHERE R.[ID]=ZOld.[ID_Работника] AND R.[ID]=ZNew.[ID_Работника]
ELSE
/* было удаление строк */
UPDATE [Работники]
SET [СуммаЗаказов]=[СуммаЗаказов]-Z.[СуммаОбщ]
FROM [Работники] R,
(SELECT [ID_Работника], SUM([Сумма]) as [СуммаОбщ]
FROM DELETED
GROUP BY [ID_Работника]) Z
WHERE R.[ID]=Z.[ID_Работника]
ELSE IF (@InsertedCount > 0)
/* была вставка строк */
UPDATE [Работники]
SET [СуммаЗаказов]=[СуммаЗаказов]+Z.[СуммаОбщ]
FROM [Работники] R,
(SELECT [ID_Работника], SUM([Сумма]) as [СуммаОбщ]
FROM INSERTED
GROUP BY [ID_Работника]) Z
WHERE R.[ID]=Z.[ID_Работника]
END
Но лучше обходиться без триггеров, реализуя логику обработки данных через процедуры.
Дата добавления: 2015-09-11; просмотров: 85 | Поможем написать вашу работу | Нарушение авторских прав |
| <== предыдущая лекция | | | следующая лекция ==> |
| Триггеры | | | Транзакции и конфликты совместного доступа |