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

Когда использовать триггеры

Триггеры, как и ограничения, можно использовать для поддержки целостности данных и деловых правил, но триггер не следует использовать как замену ограничения, когда достаточно использовать только ограничение. (Ограничения описаны в лекции 16.) Например, вам не нужно создавать триггер, который проверяет наличие значения в колонке первичного ключа одной таблицы, чтобы определить, можно ли вставить это значение в соответствующую колонку другой таблицы; в этой ситуации прекрасно подойдет ограничение FOREIGN KEY. Однако вам может потребоваться триггер для каскадирования изменений, вносимых в связанные таблицы базы данных. Например, вы можете создать триггер DELETE по колонке title_id в таблице titles базы данных pubs, который удалит строки в таблицах sales, roysched и titleauthor, если удаляется соответствующая строка в таблице titles. (Мы увидим в следующем разделе, как создать этот триггер DELETE.)

Вы можете также использовать триггеры для проведения более сложных проверок по данным, чем это допускается при использовании ограничения CHECK. (Ограничения CHECK подробно описываются в лекции 16.) Дело в том, что триггеры могут обращаться к колонкам таблиц, отличных от таблицы, по которой они определены, в то время как ограничения CHECK действуют в рамках только одной таблицы.

Триггеры также полезно использовать для выполнения нескольких операций в ответ на одно событие модификации данных. Если вы создаете несколько триггеров для одного типа события модификации данных, то все эти триггеры будут активизироваться, когда происходит это событие. (Следует помнить, что если несколько триггеров определено по таблице или представлению для какого-либо события, то каждый триггер должен иметь уникальное имя.)

Вы можете создать один триггер, который будет активизироваться для нескольких типов событий модификации данных. Этот триггер будет активизироваться каждый раз, когда будет возникать событие, для которого он определен. Поэтому триггер, определенный по определенной таблице или представлению для вставок, изменений и удалений, будет активизироваться при каждом возникновении любого из этих событий по данной таблице или представлению.

Когда вы создаете какой-либо триггер, SQL Server создает две специальные временные таблицы. Вы можете обращаться к этим таблицам при написании T-SQL-программы, которая образует определение этого триггера. Эти таблицы всегда находятся в памяти и являются локальными по отношению к данному триггеру, и каждый триггер имеет доступ только к своим временным таблицам. Эти временные таблицы являются копиями таблицы базы данных, по которой определяется данный триггер. Вы можете использовать эти временные таблицы, чтобы увидеть влияние, оказанное каким-либо событием модификации данных на исходную таблицу. Вы увидите примеры этих специальных таблиц (с именами deleted и inserted) в следующем разделе.

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

Теперь, зная, что такое триггеры и когда они используются, перейдем к особенностям создания триггеров. В этом разделе мы рассмотрим сначала метод создания триггеров с помощью T-SQL и затем – метод с использованием Enterprise Manager. Чтобы использовать Enterprise Manager для создания триггеров, вам нужно знать программирование с помощью T-SQL, как и в случае, когда вы используете Enterprise Manager для создания других типов хранимых процедур.

Использование оператора CREATE TRIGGER

Чтобы использовать T-SQL для создания триггера, нужно применить оператор CREATE TRIGGER. (В методе с Enterprise Manager также используется этот оператор.) Оператор CREATE TRIGGER имеет следующий синтаксис:

CREATE TRIGGER имя_триггера
ON {таблица | представление}
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF}  
    {[DELETE] [,] [INSERT] [,] [UPDATE]}  
        [WITH APPEND]  
        [NOT FOR REPLICATION]  
AS
        оператор_sql [...n]

Как видно из этого описания, вы можете создать триггер для оператора INSERT, UPDATE, DELETE, INSTEAD OF или AFTER или для любой комбинации из этих пяти операторов. Вы должны задать хотя бы одну опцию с предложением FOR. Это предложение указывает, возникновение какого типа события модификации данных (или типов событий) по указанной таблице приведет к активизации данного триггера.

При вызове триггера будут выполнены операторы SQL, указанные после ключевого слова AS. Вы можете поместить сюда несколько операторов, включая программные конструкции, такие как IF и WHILE. В определении триггера не допускаются следующие операторы:

Использование таблиц deleted и inserted

Как уже говорилось, при создании триггера вы имеете доступ к двум временным таблицам с именами deleted и inserted. Их называют таблицами, но они отличаются от реальных таблиц баз данных. Они хранятся в памяти, а не на диске.

Эти две таблицы имеют одинаковую структуру с таблицей (одинаковые колонки и типы данных), по которой определяется данный триггер. Таблица deleted содержит копии строк, на которые повлиял оператор DELETE или UPDATE. Строки, удаляемые из таблицы данного триггера, перемещаются в таблицу deleted. После этого к данным таблицы deleted можно осуществлять доступ из данного триггера. Таблица inserted содержит копии строк, добавленных к таблице данного триггера при выполнении оператора INSERT или UPDATE. Эти строки добавляются одновременно в таблицу триггера и в таблицу inserted. Поскольку оператор UPDATE обрабатывается как DELETE, после которого следует INSERT, то при использовании оператора UPDATE старые значения строк копируются в таблицу deleted, а новые значения строк – в таблицу триггера и в таблицу inserted.

Если вы попытаетесь проверить содержимое таблицы deleted из триггера, который активизирован в результате выполнения оператора INSERT, эта таблица окажется пустой, но сообщение об ошибке не возникнет. Выполнение оператора INSERT не приводит к копированию значений строк в таблицу deleted. Аналогичным образом, если вы попытаетесь проверить содержимое таблицы inserted из триггера, который активизирован в результате выполнения оператора DELETE, эта таблица окажется пустой. Выполнение оператора DELETE не приводит к копированию значений строк в таблицу inserted. И здесь просмотр пустой таблицы не приведет к сообщению об ошибке; поэтому для использования этих таблиц с целью просмотра результатов модификаций убедитесь в том, что для доступа из триггера у вас выбрана соответствующая таблица.

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

Чтобы увидеть, как работает триггер, создадим простую таблицу с определенным по ней триггером, который печатает определенный текст при каждой модификации. Программа 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 и удаление не произойдет.

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

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

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

USE pubs
GO
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") 
GO

Вы увидите следующие результаты. В первом наборе результатов показана строка, выбранная из таблицы 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)

Создание триггера типа 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 обрабатывал ненужные операторы.

Создание триггера INSTEAD OF

Триггер INSTEAD OF позволяет вам управлять тем, что происходит, когда выполняются функции INSERT, UPDATE или DELETE. Триггер INSTEAD OF используется в первую очередь при модификации объединенного представления (union view). Обычно объединенные представления недоступны для модификации, поскольку SQL Server "не знает", какую базовую (underlying) таблицу или таблицы нужно модифицировать. Для обхода этой ситуации вы определяете по данному представлению триггер INSTEAD OF, позволяющий модифицировать базовые (underlying) таблицы. Рассмотрим один пример. Следующие операторы T-SQL создают представление под именем TitlesByAuthor со ссылкой на таблицы authors, titles и titleauthor. (О создании представлений см. лекцию 18.)

USE pubs
GO

CREATE   VIEW TitlesByAuthor
AS
SELECT   authors.au_id, authors.au_lname, titles.title
FROM     authors INNER JOIN
           titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN
    titles ON titleauthor.title_id = titles.title_id 
GO

Теперь после создания представления мы используем следующий набор T-SQL для вывода всех строк (они также показаны здесь), отвечающих критериям данного представления:

USE pubs
GO

SELECT *
FROM   TitlesByAuthor
GO

au_id             au_lname           title
————— ——————— —————————————————————
238-95-7766     Carson             But Is It User Friendly?
724-80-9391     MacFeather         Computer Phobic AND Non-Phobic Individuals:
756-30-7391     Karsen             Computer Phobic AND Non-Phobic Individuals:
267-41-2394     O’Leary            Cooking with Computers:
724-80-9391     MacFeather         Cooking with Computers: 
486-29-1786     Locksley           Emotional Security: A New Algorithm 
648-92-1872     Blotchet-Halls       Fifty Years in Buckingham Palace Kitchens
899-46-2035     Ringer             Is Anger the Enemy?
998-72-3567     Ringer             Is Anger the Enemy? 
998-72-3567     Ringer             Life Without Fear 
486-29-1786     Locksley           Net Etiquette 
807-91-6654     Panteley           Onions, Leeks, and Garlic: 
172-32-1176       White              Prolonged Data Deprivation: Four Case Studies
427-17-2319     Dull               Secrets of Silicon Valley
846-92-7186     Hunter             Secrets of Silicon Valley
712-45-1867     del Castillo       Silicon Valley Gastronomic Treats
274-80-9391     Straight           Straight Talk About Computers
267-41-2394     O’Leary            Sushi, Anyone?
472-27-2349     Gringlesby         Sushi, Anyone?
672-71-3249     Yokomoto           Sushi, Anyone?
213-46-8915     Green              The Busy Executive’s Database Guide
409-56-7008     Bennet             The Busy Executive’s Database Guide
722-51-5454     DeFrance           The Gourmet Microwave
899-46-2035     Ringer             The Gourmet Microwave
213-46-8915     Green              You Can Combat Computer Stress!


(25 row(s) affected)

Если вы попытаетесь удалить из этого представления строку, в которой значение колонки au_lname равно Carson, то появится следующее сообщение:

Server: Msg 4405, Level 16, State 1, Line 1 View or function 'TitlesByAuthor' is not updatable 
because the FROM clause names multiple tables. (Представление или 
функция 'TitlesByAuthor' недоступна для модифицирования, 
поскольку предложение FROM ссылается names на несколько таблиц)

Чтобы обойти эту ситуацию, мы создадим для операции удаления триггер INSTEAD OF. Следующий набор операторов T-SQL создает триггер INSTEAD OF с именем Delete_It:

USE pubs
GO
 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = 'Delete_It' AND 
                              type = 'TR') 
DROP TRIGGER Delete_It 
GO 
 
CREATE TRIGGER Delete_It 
ON TitlesByAuthor 
INSTEAD OF DELETE 
AS 
PRINT       'Row from authors before deletion...' 
SELECT      au_id, au_lname, city, state 
FROM        authors 
WHERE       au_lname = 'Carson' 
PRINT       'Deleting row from authors...' 
UPDATE      authors 
SET           au_lname = 'DELETED' 
WHERE       au_lname = 'Carson' 
PRINT       'Verifying deletion...' 
SELECT      au_id, au_lname, city, state 
FROM        authors 
WHERE       au_lname = 'Carson' 
GO

И если теперь мы введем операторы для удаления строки Carson из этого представления, то произойдет активизация триггера INSTEAD OF и мы получим следующие выходные результаты:

Row from authors before deletion...  (Строка из authors перед удалением)
au_id           au_name             city          state
-----------------------------------------------------------------------------------
238-95-7766   Carson                Berkeley         CA

(1 row(s) affected)
Deleting row from authors...            (Удаление строки из authors)
(1 row(s) affected)
Verifying deletion...:                       (Проверка удаления)
au_id           au_name             city             state
-----------------------------------------------------------------------------------

(0 row(s) affected)

Использование триггеров AFTER

Как уже говорилось ранее в этой лекции, триггер AFTER – это просто триггер, который активизируется после завершения указанного события модификации данных. Если у вас определен по таблице более чем один триггер AFTER для определенного события или набора событий, то вы можете задать, какой триггер будет активизироваться первым и какой – последним. Любые другие триггеры, определенные по данной таблице для этого события или набора событий, будут активизироваться в случайном порядке. Первый и последний триггеры задаются с помощью оператора T-SQL sp_settriggerorder. Этот оператор имеет следующий синтаксис:

sp_settriggerorder   [@triggername =] 'имя_триггера',
                             [@order=] {'first' | 'last' | 'none'}

Рассмотрим один пример. Предположим, что у нас четыре триггера, определенных по таблице: MyTrigger, MyOtherTrigger, AnotherTrigger и YetAnotherTrigger. Мы хотим убедиться, что после запускающего события AnotherTrigger активизируется первым и MyTrigger активизируется последним. Мы вводим следующие операторы T-SQL:

sp_settriggerorder @triggername = 'AnotherTrigger', @order = 'first'
go
sp_settriggerorder @triggername = 'MyTrigger', @order = 'last'
go
sp_settriggerorder @triggername = 'MyOtherTrigger', @order = 'none'
go
sp_settriggerorder @triggername = 'YetAnotherTrigger', @order = 'none'
go

Обозначение "none" для MyOtherTrigger и YetAnotherTrigger указывает, что SQL Server должен активизировать эти триггеры случайным образом после активизации AnotherTrigger и до активизации MyTrigger. Поскольку этот случайный порядок активизации принят для триггеров по умолчанию, то вы не обязаны явно запускать оператор sp_settriggerorder для триггеров, которые запускаются случайным образом.

Использование вложенных триггеров

Вложенные триггеры – это триггеры, которые активизируются другими триггерами. Они отличаются от рекурсивных триггеров, которые активизируют сами себя. Вложенный триггер инициируется, когда событие модификации данных внутри другого триггера активизирует этот вложенный триггер. Как и в SQL Server 7, SQL Server 2000 допускает до 32 уровней вложенности триггеров. Один триггер активизирует второй триггер, который, в свою очередь, активизирует третий триггер, и т.д. вплоть до 32-го триггера. Использование вложенных триггеров разрешается в SQL Server 2000 по умолчанию. Вы можете указать, разрешается ли в SQL Server использование вложенных триггеров, задав соответствующий параметр конфигурации сервера для вложенных триггеров Например, чтобы блокировать использование вложенных триггеров, выполните следующую команду:

sp_configure "nested triggers", 0
GO

Значение 0 блокирует использование вложенных триггеров; значение 1 разрешает их использование. Рассмотрим пример использования вложенных триггеров. В этом примере мы создадим вложенные триггеры, которые будут выполнять каскадные удаления при удалении заголовка книги из таблицы titles. (См. раздел "Создание триггера типа DELETE" ранее.) Мы создали отдельный триггер, который выполняет эту операцию. Сначала мы удалим этот триггер, чтобы он не активизировался. Затем мы создадим три триггера. Второй и третий триггеры будут вложенными триггерами. Первый триггер будет активизировать второй триггер, который будет активизировать третий триггер. Вот эта программа:

USE pubs
GO 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "Delete_Title" AND 
                              type = "TR") 
DROP TRIGGER Delete_Title 
GO 
 
CREATE TRIGGER TR_on_titles 
ON titles 
FOR DELETE 
AS 
DELETE   sales 
FROM     sales, deleted 
WHERE    sales.title_id = deleted.title_id 
PRINT    "Deleted from sales" 
GO 

CREATE TRIGGER TR_on_sales 
ON sales 
DELETE   roysched 
FROM     roysched, deleted 
WHERE    roysched.title_id = deleted.title_id 
PRINT    "Deleted from roysched" 
GO 
 
CREATE TRIGGER TR_on_roysched 
ON roysched 
DELETE   titleauthor 
FROM     titleauthor, deleted 
WHERE    titleauthor.title_id = deleted.title_id 
PRINT    "Deleted from titleauthor" 
GO

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

DELETE
FROM     titles 
WHERE    title_id = "PS7777" 
GO

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

(2 row(s) affected)  

(1 row(s) affected)  

Deleted from titleauthor     (Удалено из titleauthor)

(2 row(s) affected)

Deleted from roysched    

(Удалено из roysched)

(1 row(s) affected)

Deleted from sales             (Удалено из sales)

(1 row(s) affected)

При сбое на любом уровне набора вложенных триггеров происходит отмена всей транзакции и откат модификаций всех данных до начала данной транзакции.

Использование Enterprise Manager

Чтобы создать триггер с помощью Enterprise Manager, вы просто вводите свои операторы T-SQL в окне Trigger Properties (Свойства триггера). Для использования этого метода выполните следующие шаги.

В окне Enterprise Manager щелкните правой кнопкой мыши на имени таблицы, по которой вы хотите создать триггер. В появившемся контекстном меню укажите All Tasks (Все задачи) и затем выберите из подменю All Tasks пункт Manage Triggers (Управление триггерами). Появится окно Trigger Properties (рис 22.1(рис 22.1) Окно Trigger Properties (Свойства триггера)

Введите в окне Text предложения T-SQL для вашего триггера. На рис. 22.2 показано окно Trigger Properties, содержащее T-SQL-текст триггера с именем (рис 22.2) Окно Trigger Properties с текстом триггера

Щелкните на кнопке Check Syntax (Проверить синтаксис) для проверки синтаксиса. При отсутствии синтаксических ошибок появится диалоговое окно, показанное на рис 22.3(рис 22.3) Диалоговое окно с сообщением об успешном завершении синтаксической проверки

Окно Trigger Properties остается открытым, что позволяет вам создавать новые триггеры по данной таблице. Если вам не нужно создавать новые триггеры, щелкните на кнопке Close (Закрыть).(рис 22.4) Имя нового триггера в раскрывающемся списке Name

Управление триггерами

Теперь, когда вы знаете, как создавать триггер, вам надо научиться управлять триггерами. В этом разделе мы рассмотрим сначала управления триггерами с помощью команд T-SQL, а затем – управление с помощью Enterprise Manager.

Управление триггерами с помощью операторов T-SQL

Имеется несколько операторов, с помощью которых можно управлять триггерами. Вы можете просматривать текст триггера, просматривать существующие триггеры по определенной таблице, изменять текст триггера, удалять триггеры и разрешать или блокировать использование триггеров. Все эти возможности описаны в данном разделе.

Просмотр текста триггера

Информацию о триггерах можно получить с помощью двух системных хранимых процедур: sp_helptext и sp_helptrigger. Процедура sp_helptext, после которой указывается имя триггера, используется для вывода на экран текста, который использовался для создания этого триггера. Например, для просмотра определения созданного ранее триггера Print_Update используйте следующий оператор:

USE MyDB
GO
sp_helptext Print_Update
GO

Результаты выглядят следующим образом:

Text 
-------------------------------------------------
CREATE TRIGGER Print_Update 
ON Bicycle_Inventory 
FOR UPDATE 
AS 
PRINT "The Bicycle Inventory table was updated."

Просмотр триггеров, имеющихся по какой-либо таблице

Для просмотра триггеров, существующих по определенной таблице (или чтобы увидеть, что нет ни одного триггера), используйте хранимую процедуру sp_helptrigger, после которой укажите имя этой таблицы. Для просмотра триггеров по таблице MyTable используйте следующий оператор:

USE MyDB
GO 
sp_helptrigger MyTable 
GO

Ниже приводятся результаты:

trigger_name     trigger_owner   isupdate     isdelete  isinsert   isafter   isinsteadof
-----------------------------------------------------------------------------------------------
Print_Update      dbo            1             0          0          1         0
 
(1 row affected)

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

Результирующие колонки isupdate, isdelete, isinsert, isafter и isinsteadof содержат значение 1, если триггер активизируется событием модификации данных, которое указано именем колонки, или 0 в противном случае. Если триггер активизируется более чем одним типом событий модификации данных, то значение 1 может быть в нескольких колонках.

Использование оператора ALTER TRIGGER

Чтобы изменить определение триггера, вы можете удалить и заново создать соответствующий триггер или использовать оператор ALTER TRIGGER. Для этого оператора используется тот же синтаксис, что и для оператора CREATE TRIGGER. Если вы модифицируете триггер, то должны переопределить весь этот триггер. Например, для изменения триггера Print_Update, чтобы он активизировался при выполнении операторов INSERT или UPDATE, которые влияют на таблицу Bicycle_Inventory, используйте следующий текст:

USE MyDB
GO
ALTER TRIGGER Print_Update 
ON Bicycle_Inventory 
FOR UPDATE, INSERT 
AS 
PRINT "Bicycle_Inventory was updated or a row was inserted" 
GO

Теперь старая версия данного триггера не существует; она заменена новой версией. Теперь этот триггер будет активизироваться при изменениях в Bicycle_Inventory и при вставке строк в эту таблицу. Ниже показаны примеры операторов, используемых для выполнения этих операций:

INSERT   INTO Bicycle_Inventory VALUES ("Trek",1,"Lance S.E.",1,0,1)
GO

UPDATE   Bicycle_Inventory
SET        in_stock = 1
WHERE    model_name = "Lance S.E."
GO

Использование оператора DROP TRIGGER

Для удаления триггера из таблицы используйте оператор DROP TRIGGER. Вы можете удалить триггер, который вам больше не нужен. Этот оператор имеет следующий синтаксис:

DROP TRIGGER имя_триггера
Чтобы удалить триггер Print_Update, используйте следующий оператор:
USE Bicycle_Inventory
GO 
DROP TRIGGER Print_Update 
GO

Теперь при попытке увидеть существующие триггеры по таблице MyTable (с помощью следующих операторов T-SQL) вы увидите, что нет ни одного триггера:

USE MyDB
GO 
sp_helptrigger MyTable 
GO

Блокирование (disabling) и разблокирование (enabling) триггеров

Вы можете использовать оператор ALTER TABLE для разблокирования и блокирования триггера без удаления определения этого триггера из таблицы. Поскольку каждый триггер создается по определенной таблице, то мы используем оператор ALTER TABLE вместо оператора ALTER TRIGGER. Создадим триггер из первого примера этой лекции, чтобы использовать его в следующем примере:

USE MyDB
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 "Bicycle_Inventory table was updated"
GO

В момент создания этот триггер автоматически становится доступен для активизации (он разблокирован). Чтобы блокировать активизацию этого триггера (до момента, когда будет снова задано разблокирование) с сохранением определения этого триггера в соответствующей таблице, используйте опцию DISABLE TRIGGER, как это показано ниже:

ALTER TABLE Bicycle_Inventory
DISABLE TRIGGER Print_Update 
GO

Теперь после этого изменения в таблице Bicycle_Inventory триггер Print_Update не будет активизироваться, пока не будет явно задано разблокирование с помощью опции ENABLE TRIGGER, как это показано ниже:

ALTER TABLE Bicycle_Inventory
ENABLE TRIGGER Print_Update
GO

Предложения ENABLE TRIGGER и DISABLE TRIGGER полезно использовать, когда вы хотите прекратить активизацию триггера, но сохранить его на тот случай, если он понадобится в будущем. Определение триггера не изменяется, поэтому вам не придется создавать его снова в будущем; вам нужно будет только разблокировать его.

Управление триггерами с помощью Enterprise Manager

Используя Enterprise Manager, вы можете управлять вашими триггерами с помощью графического интерфейса. Но вам по-прежнему нужно знать, как создаются программы на языке T-SQL.

Удаление триггера

Чтобы удалить триггер с помощью Enterprise Manager, выполните следующие шаги.

Модифицирование триггера

Чтобы модифицировать триггер с помощью Enterprise Manager, выполните следующие шаги.

Заключение

В этой лекции вы ознакомились со специальным типом хранимых процедур, который называется триггером, и узнали о пяти типах триггеров, которые могут быть созданы по определенной таблице: INSERT, UPDATE, DELETE, AFTER и INSTEAD OF. Вы также узнали, как создавать и модифицировать эти триггеры с помощью операторов T-SQL и Enterprise Manager. В лекции 23 вы узнаете, как осуществлять доступ к SQL Server 2000 из Internet с помощью таких технологий, как ADO и XML.