Создание триггера типа DELETE — до 10 мин

Пример создания триггера

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

CREATE TABLE Bicycle_Inventory

(

make_name char(10) NOT NULL,

make_id tinyint NOT NULL,

model_name char(12) NOT NULL,

model_id tinyint NOT NULL,

in_stock tinyint NOT NULL,

on_order tinyint NULL,

)

GO

IF EXISTS (SELECT name FROM sysobjects

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

DROP TRIGGER Print_Update

GO

CREATE TRIGGER Print_Update

ON Bicycle_Inventory

FOR UPDATE

AS

PRINT "The Bicycle_Inventory table was updated"

GO

Чтобы проверить триггер, выполним вставку строки в эту таблицу и затем модифицируем ее:

INSERT INTO Bicycle_Inventory VALUES ("Trek",1,"5500",5,1,0)

GO

UPDATE Bicycle_Inventory SET make_id = 2

WHERE model_name = "5500"

GO

Будет возвращено сообщение "The Bicycle_Inventory table was updated", так как в результате выполнения оператора UPDATE был запущен триггер. В данном примере мы задали в нашем триггере вывод сообщения, чтобы можно было увидеть работу этого триггера. Но обычно не требуется, чтобы триггер возвращал выходные данные. Однако в определенных обстоятельствах это может оказаться полезным. Например, предположим, что вы создаете триггер типа UPDATE, который выполняет свои операторы, только когда в указанную колонку заносится определенное значение, но эта модификация происходит неверно. Если добавить в триггер оператор PRINT, который выводит значение этой колонки до выполнения других операторов триггера, это, видимо, поможет определить, где лежит источник проблемы – в логике самого триггера или в модифицируемых данных.

Перейдем к более сложному примеру – триггер типа DELETE, который каскадирует изменения в связанные таблицы. Мы создадим триггер, который будет удалять строки из таблиц sales, roysched и titleauthor базы данных pubs, когда соответствующая строка удаляется из таблицы titles. Мы будем использовать таблицу deleted, чтобы указывать, какие строки нужно удалить из связанных таблиц. (Напомним, что при удалении какой-либо строки из таблицы триггера эта строка копируется в таблицу deleted; затем вы можете проверить содержимое таблицы deleted и удалить соответствующие записи в других таблицах.) Чтобы этот триггер мог работать, нам нужно было бы удалить ограничения FOREIGN KEY из таблиц titleauthor, roysched и sales, которые связаны с колонкой title_id таблицы titles. В данном примере мы создадим триггер так, как будто этих ограничений FOREIGN KEY не существует. Если все же попытаться удалить строку из таблицы titles, не удалив ограничений FOREIGN KEY, то вы получите сообщение об ошибке от SQL Server и удаление не произойдет.

Ниже показана программа T-SQL для этого триггера:

CREATE TRIGGER Delete_Title ON titles FOR DELETE

AS

DELETE sales

FROM sales, deleted

WHERE sales.title_id = deleted.title_id

PRINT "Deleted from sales"

DELETE roysched

FROM roysched, deleted

WHERE roysched.title_id = deleted.title_id

PRINT "Deleted from roysched"

DELETE titleauthor

FROM titleauthor, deleted

WHERE titleauthor.title_id = deleted.title_id

PRINT "Deleted from titleauthor"

GO

Чтобы проверить этот триггер, используйте оператор DELETE следующим образом:

DELETE titles WHERE title_id = "PC1035"

Если выполнить этот оператор DELETE, то произойдет активизация триггера (при условии, что вы удалили отмеченные выше ссылки на внешние ключи [FOREIGN KEY]). Вы увидите сообщение с количеством затронутых строк для события модификации данных по таблице titles, после которого следуют сообщения, заданные в трех операторах PRINT из этого триггера, и сообщения о количестве затронутых строк в трех других таблицах; эти выходные сообщения показаны ниже:

(1 row(s) affected)

Deleted from sales

(5 row(s) affected)

Deleted from roysched

(1 row(s) affected)

Deleted from titleauthor

(1 row(s) affected)

Еще одно применение таблицы deleted – это сохранение всех строк, удаленных из таблицы, в резервной таблице для последующего анализа данных. Например, чтобы сохранить строки, удаленные из таблицы roysched, в новой таблице с именем roysched_backup, используйте следующую программу:

CREATE TABLE roysched_backup

(title_id tid NOT NULL,

lorange int NULL,

hirange int NULL,

royalty int NULL)

CREATE TRIGGER tr_roysched_backup ON roysched FOR DELETE

AS

INSERT INTO roysched_backup SELECT * FROM deleted

GO

SELECT * FROM roysched_backup

GO

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

Дата добавления: 2014-01-11