Создание и использование триггеров

Создание триггера типа UPDATE

Теперь создадим UPDATE -триггер, который будет просматривать колонку price (цена) при модификации таблицы titles, чтобы убедиться, что цена книги не возросла более чем на 10 процентов. В противном случае будет использован оператор ROLLBACK, который выполнит откат данного триггера и оператора, вызвавшего триггер. Если триггер активизирован из транзакции, то произойдет откат всей транзакции. Таблицы deleted и inserted используются в данном примере для проверки изменения цены. Ниже приводится определение этого триггера:

USE pubs
GO
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "Update_Price_Check" AND 
                              type = "TR") 
DROP TRIGGER Update_Price_Check  
GO 
 
CREATE TRIGGER Update_Price_Check 
ON titles 
FOR UPDATE 
AS 
DECLARE   @orig_price money, @new_price money 
SELECT      @orig_price = price from deleted 
PRINT       "orig price ="  
PRINT       CONVERT(varchar(6),@orig_price) 
SELECT      @new_price = price from inserted 
PRINT       "new price ="  
PRINT       CONVERT(varchar(6),@new_price) 
IF (@new_price > (@orig_price * 1.10)) 
BEGIN 
      PRINT "Rollback occurred" 
      ROLLBACK 
END 
ELSE  
PRINT "Price is OK" 
GO

Чтобы проверить этот триггер, выполните сначала следующие операторы, чтобы проверить текущую цену книги с идентификатором заголовка (title_id) BU1111:

SELECT   price
FROM     titles
WHERE    title_id = "BU1111" 
GO

Цена составляет $11.95. Теперь попробуем увеличить цену на 15 процентов с помощью следующих операторов:

UPDATE   titles
SET        price = price * 1.15
WHERE    title_id = "BU1111"
GO

Вы увидите следующие результаты:

orig price = (исходная цена)
11.95
new price =  (новая цена)
13.74
Rollback occurred (Произошел откат)

Произошла активизация триггера, который вывел исходную цену и новую цену и выполнил откат (поскольку цена возросла более чем на 10 процентов).

Теперь снова проверим цену, чтобы убедиться, что был выполнен откат этой модификации. Используйте следующий T-SQL-набор:

SELECT   price
FROM     titles
WHERE    title_id = "BU1111" 
GO

Цена снова стала равной $11.95, а это означает, что был выполнен откат модификации.

Теперь увеличим цену на 9 процентов и убедимся, что цена изменена. Для этой модификации используется следующий T-SQL-набор:

UPDATE   titles
SET        price = price * 1.09 
WHERE    title_id = "BU1111" 
GO 
 
SELECT   price 
FROM     titles 
WHERE    title_id = "BU1111" 
GO

Цена стала равной $13.03, и поскольку изменение составило меньше 10 процентов, то триггер не инициировал откат.

Создавая триггер UPDATE, вы можете задать, чтобы этот триггер выполнял определенные операторы только в случае модификации определенной колонки или колонок. Например, давайте снова создадим предыдущий триггер, но на этот раз будет использовать предложение IF UPDATE, чтобы указать, что триггер проверяет колонку price, только если была обновлена сама эта колонка:

USE pubs
GO
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "Update_Price_Check" AND 
                              type = "TR") 
DROP TRIGGER Update_Price_Check  
GO 
 
CREATE TRIGGER Update_Price_Check 
ON titles 
FOR UPDATE 
AS 
IF UPDATE (price) 
BEGIN 
DECLARE   @orig_price money, @new_price money 
      SELECT      @orig_price = price 
      FROM        deleted 
      PRINT       "orig price ="  
      PRINT       CONVERT(varchar(6),@orig_price) 
      SELECT      @new_price = price 
      FROM        inserted 
      PRINT       "new price ="  
      PRINT CONVERT(varchar(6),@new_price) 
      IF (@new_price > (@orig_price * 1.10)) 
      BEGIN 
            PRINT "Rollback occurred" 
            ROLLBACK 
      END 
      ELSE  
      PRINT "Price is OK" 
END 
GO

Теперь в случае модификации одной или нескольких колонок в таблице titles, за исключением колонки price, триггер пропустит операторы между ключевыми словами BEGIN и END в операторе IF, т.е. фактически будет пропущен весь триггер.

Чтобы проверить этот триггер, выполните следующие операторы T-SQL, модифицирующие сумму продаж за год (значение в колонке ytd_sales) для книги со значением BU1111 в колонке title_id.

UPDATE   titles
SET        ytd_sales = 123
WHERE    title_id = "BU1111"
GO

Отметим, что в выходные результаты не будут включены никакие сообщения, указанные в операторах PRINT данного триггера. Сам триггер активизируется, так как мы модифицировали таблицу titles. Но поскольку была модифицирована колонка ytd_sales, а не колонка price, то результатом условия IF было значение FALSE. Поэтому операторы данного триггера не выполнялись. Этот метод препятствует тому, чтобы SQL Server обрабатывал ненужные операторы.