НОУ ИНТУИТ | Основы SQL. Лекция 14: Триггеры: создание и применение

Опубликован: 10.09.2004 | Уровень: для всех | Доступ: свободно | ВУЗ: Ульяновский государственный университет

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TRIGGER Триггер_ins
ON Сделка FOR INSERT
AS
DECLARE @x INT, @y INT
IF @@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
    END
END

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

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

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

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

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

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

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

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

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

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

CREATE TRIGGER Триггер_upd
ON Сделка FOR UPDATE
AS
DECLARE @x INT, @x_old INT, @y INT, @y_old INT
-- курсор с новыми значениями
DECLARE CUR1 CURSOR FOR
    SELECT КодТовара,Количество
    FROM inserted
-- курсор со старыми значениями
DECLARE CUR2 CURSOR FOR
    SELECT КодТовара,Количество
    FROM deleted
OPEN CUR1
OPEN 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_old
END
CLOSE CUR1
CLOSE CUR2
DEALLOCATE CUR1
DEALLOCATE CUR2

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

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

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

ALTER TRIGGER Триггер_upd
ON Сделка FOR UPDATE
AS
DECLARE @x INT, @x_old INT, @y INT, 
        @y_old INT ,@o INT
DECLARE CUR1 CURSOR FOR
    SELECT КодТовара,Количество
    FROM inserted
DECLARE CUR2 CURSOR FOR
    SELECT КодТовара,Количество
    FROM deleted
OPEN CUR1
OPEN 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 CUR2
          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_old
END
CLOSE CUR1
CLOSE CUR2
DEALLOCATE CUR1
DEALLOCATE CUR2

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

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

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

ALTER TRIGGER Триггер_upd
ON Сделка INSTEAD OF UPDATE
AS
DECLARE @k INT, @k_old INT
DECLARE @x INT, @x_old INT, @y INT
DECLARE @y_old INT ,@o INT
DECLARE CUR1 CURSOR FOR
    SELECT КодСделки, КодТовара,Количество
    FROM inserted
DECLARE CUR2 CURSOR FOR
    SELECT КодСделки, КодТовара,Количество
    FROM deleted
OPEN CUR1
OPEN 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_old
END
CLOSE CUR1
CLOSE CUR2
DEALLOCATE CUR1
DEALLOCATE CUR2

Пример 14.6. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

Дальше >>

Авторизоваться

Основы SQL

Триггеры: создание и применение

Вопросы и ответы