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

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

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

USE MyDB
GO
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

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

Примечание. Если вы не возражаете против изменения вашей базы данных pubs, попытайтесь удалить ограничения FOREIGN KEY самостоятельно и затем создать данный триггер. Проще всего удалить ограничения FOREIGN KEY с помощью схемы базы данных в окне Enterprise Manager. (Этот процесс описан в "Создание и использование умолчаний, ограничений и правил" .) Не забудьте удалить ограничения FOREIGN KEY, связанные с title_id.

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

USE pubs
GO
IF EXISTS   (SELECT       name 
                 FROM        sysobjects 
                 WHERE       name = "Delete_Title" AND 
                                 type = "TR") 
DROP TRIGGER Delete_Title 
GO 
 
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"
GO

Если выполнить этот оператор 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, используйте следующую программу:

USE pubs
GO
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, но обязаны использовать одинаковые типы данных для обеих таблиц, чтобы обеспечить совместимость.