3.2.5Создание триггера типа insert — до 5 мин.

В этом примере мы создадим триггер типа INSERT (триггер, который активизируется при выполнении оператора INSERT) по таблице sales. Этот триггер при вставке строки в таблицу sales будет модифицировать колонку ytd_sales в таблице titles добавляя в нее значение, которое было помещено в колонку qty таблицы sales. Этот триггер запрашивает таблицу inserted, чтобы получить значение qty, которое было помещено в таблицу sales. Мы включим в этот запрос оператор SELECT *, с помощью которого увидим, что содержит таблица inserted. Ниже приводится T-SQL-текст для этого триггера:

IF EXISTS (SELECT name FROM sysobjects

WHERE name = "Update_ytd_sales" AND type = "TR")

DROP TRIGGER Update_ytd_sales

GO

CREATE TRIGGER Update_ytd_sales ON sales FOR INSERT

AS

SELECT * FROM inserted

UPDATE titles SET ytd_sales = ytd_sales + qty

FROM inserted

WHERE titles.title_id = inserted.title_id

GO

Отметим, что мы использовали в операторе UPDATE предложение FROM источник_для_таблицы (FROM inserted), чтобы указать, что значение qty должно поступать из таблицы inserted. Теперь выполним следующий оператор INSERT для просмотра результатов из этого триггера:

INSERT INTO sales VALUES(7066, 1, "March 7, 2000", 100,

"Net 30", "BU1111")

Вы увидите следующие результаты. В первом наборе результатов показана строка, выбранная из таблицы inserted, и второе сообщение "1 row(s) affected" получено из оператора UPDATE.

stor_id ord_num ord_date qty payterms title_id

----------------------------------------------------------------------------------------

7066 1 2000-03-07 00:00:00.000 100 Net 30 BU1111

(1 row(s) affected)

(1 row(s) affected)

3.2.6Создание триггера типа update — до 10 мин.

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

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 =", CONVERT(varchar(6),@orig_price)

SELECT @new_price = price from inserted

PRINT "new price =", 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"

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

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

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

orig price = (исходная цена)

11.95

new price = (новая цена)

13.74

Rollback occurred (Произошел откат)

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

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

SELECT price FROM titles WHERE title_id = "BU1111"

Цена снова стала равной $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, только если была обновлена сама эта колонка:

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"

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