Опубликован: 11.12.2006 | Доступ: свободный | Студентов: 5820 / 381 | Оценка: 4.42 / 3.86 | Длительность: 57:15:00
Лекция 22:

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

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

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

Вы можете также использовать триггеры для проведения более сложных проверок по данным, чем это допускается при использовании ограничения CHECK. (Ограничения CHECK подробно описываются в "Создание и использование умолчаний, ограничений и правил" .) Дело в том, что триггеры могут обращаться к колонкам таблиц, отличных от таблицы, по которой они определены, в то время как ограничения 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. В определении триггера не допускаются следующие операторы:

  • ALTER DATABASE
  • CREATE DATABASE
  • DISK INIT
  • DISK RESIZE
  • DROP DATABASE
  • LOAD DATABASE
  • LOAD LOG
  • RECONFIGURE
  • RESTORE DATABASE
  • RESTORE LOG
Использование таблиц 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. И здесь просмотр пустой таблицы не приведет к сообщению об ошибке; поэтому для использования этих таблиц с целью просмотра результатов модификаций убедитесь в том, что для доступа из триггера у вас выбрана соответствующая таблица.

Примечание. Значения таблиц inserted и deleted доступны только из триггера. После завершения работы триггера эти таблицы больше не доступны.