Инструкции триггеров используют две особые таблицы: deleted (удаленные значения) и inserted (вставленные значения). По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. SQL Server автоматически создает и управляет ими. Эти временные таблицы, находящиеся в оперативной памяти, используются для проверки результатов изменений данных и для установки условий срабатывания триггеров. Изменять данные в этих таблицах или выполнять над ними операции языка DDL, например инструкцию CREATE INDEX, нельзя.
В триггерах DML таблицы inserted и deleted в основном используются для выполнения следующих операций.
- Расширение ссылочной целостности между таблицами.
- Вставка или обновление данных в базовых таблицах соответствующего представления.
- Проверка на ошибки и принятие соответствующих мер в связи с появлением ошибок.
- Поиск различий между состояниями таблицы до и после изменения данных и принятие соответствующих мер в зависимости от наличия или отсутствия различий.
В таблице deleted находятся копии строк, с которыми работали инструкции DELETE или UPDATE. При выполнении инструкции DELETE или UPDATE происходит удаление строк из таблицы триггера и их перенос в таблицу deleted. У таблицы deleted обычно нет общих строк с таблицей триггера.
В таблице inserted находятся копии строк, с которыми работали инструкции INSERT или UPDATE. При выполнении транзакции вставки или обновления происходит одновременное добавление строк в таблицу триггера и в таблицу inserted. Строки таблицы inserted являются копиями новых строк таблицы триггера.
Транзакция обновления аналогична выполнению операции удаления с последующим выполнением операции вставки; сначала старые строки копируются в таблицу deleted, а затем новые строки копируются в таблицу триггера и в таблицу inserted.
Следующие правила относятся к инструкциям INSERT или UPDATE, ссылающимся на таблицу с триггером INSTEAD OF, которые должны предоставлять такие значения для столбцов, как если бы в таблице не было триггера INSTEAD OF.
- Не могут быть заданы значения для вычисляемых столбцов и для столбцов с типом данных timestamp.
- Если параметр IDENTITY_INSERT для этой таблицы не равен ON, то значения для столбцов со свойством IDENTITY не могут быть заданы. Когда значение параметра IDENTITY_INSERT равно ON, инструкции INSERT должны сами задавать это значение.
- Инструкции INSERT должны определять значения для всех столбцов со свойством NOT NULL, не имеющих ограничений DEFAULT.
- Для любого столбца, кроме вычисляемых столбцов, IDENTITY и столбов типа timestamp, определение значений является необязательным, если разрешены NULL значения или если какой-либо столбец со свойством NOT NULL имеет ограничение DEFAULT.
Если инструкция INSERT, UPDATE или DELETE ссылается на представление, для которого определен триггер INSTEAD OF, компонент Database Engine вызывает триггер вместо того, чтобы предпринять какое-либо прямое действие по отношению к таблице. Триггер использует сведения, представленные в таблицах inserted и deleted, для создания инструкций, необходимых для выполнения требуемых действий в базовых таблицах, даже в том случае, если формат данных в таблицах inserted и deleted, созданных для представления, отличается от формата данных базовой таблицы.
Формат таблиц inserted и deleted триггера INSTEAD OF, заданного для представления, совпадает со списком выборки инструкции SELECT, заданной для представления.
Например:
CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
Результирующий набор для данного представления состоит из трех столбцов: одного столбца типа int и двух столбцов типа nvarchar. Таблицы inserted и deleted триггера INSTEAD OF, заданного для представления, также содержат столбец типа int с именем EmployeeID, столбец типа nvarchar с именем LName и столбец типа nvarchar с именем FName.
Список выборки представления также может содержать выражения, которые не сопоставлены напрямую с каким-либо одним столбцом базовой таблицы. Некоторые выражения представления, такие как вызов функции или константы, могут не ссылаться на столбцы и просто пропускаться. Сложные выражения могут ссылаться на несколько столбцов, однако таблицы inserted и deleted содержат только по одному значению для каждой вставляемой строки. Такие же проблемы появляются и в простых выражениях представления, если они ссылаются на вычисляемый столбец со сложным выражением.
Если действия триггера зависят от количества строк, данные в которых были изменены, можно воспользоваться проверками системных переменных (например, проверкой @@ROWCOUNT).
Создание триггеров
Триггер DML нельзя создать для временной или системной таблицы, хотя он может ссылаться на временные таблицы. Создание триггеров в базе данных осуществляется командой CREATE TRIGGER, данная команда должна быть первой инструкцией пакета; все остальные инструкции пакета интерпретируются как часть определения инструкции CREATE TRIGGER.
Несмотря на то, что инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE (которая удаляет все строки), она не приводит к срабатыванию триггеров DELETE, потому что инструкция TRUNCATE TABLE не регистрируется.
Триггеры INSTEAD OF DELETE и INSTEAD OF UPDATE нельзя определить для таблицы, которая имеет внешний ключ, определенный с действием DELETE или UPDATE.
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
AS { sql_statement [; ] [,...n ] }
Trigger_name
Имя триггера.
table | view
Таблица или представление, в которых выполняется триггер DML. На представление может ссылаться только триггер INSTEAD OF.
WITH ENCRYPTION
Затемняет текст инструкции CREATE TRIGGER хранящийся в базе данных. Непривилегированные пользователи не смогут получить исходный текст триггера.
EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, используемые триггером.
FOR | AFTER
Тип AFTER указывает, что триггер срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает.
Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию.
Триггеры AFTER не могут быть определены на представлениях.
INSTEAD OF
Указывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопределяя таким образом действия выполняемой инструкции триггера.
На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF.
{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }
Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.
Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ограничение целостности внешнего ключа с указанием каскадного действия ON DELETE. Точно так же параметр UPDATE не разрешен в таблицах, имеющих ограничение целостности внешнего ключа с указанием каскадного действия ON UPDATE.
Sql_statement
Действия триггера, указанные в инструкциях языка Transact-SQL.
Пример:
В следующем примере создается триггер. Этот триггер проверяет уровень кредитоспособности поставщика при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader. Чтобы получить оценку кредитоспособности поставщика, связанного с заказом на покупку, который только что был добавлен, таблица inserted должна ссылаться на таблицу Vendor и быть связана с ней. В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не производится.
CREATE TRIGGER LowCredit ON PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p
INNER JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
RAISERROR ('Низкая кредитоспособность покупателя.', 16, 1)
ROLLBACK TRANSACTION
END
GO
Изменение триггеров
Изменение существующих триггеров в базе данных осуществляется командой ALTER TRIGGER.
ALTER TRIGGER trigger_name
ON { table | view }
[ WITH [ ENCRYPTION ] [ EXECUTE AS Clause ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
AS { sql_statement [; ] [,...n ] }
Параметры команды ALTER TRIGGER идентичны параметрам команды CREATE TRIGGER.
Если триггер был создан с опцией шифрования текста инструкции CREATE TRIGGER (WITH ENCRYPTION), при изменении триггера, если требуется сохранить текст инструкции в зашифрованном состоянии, должна быть также указана опция WITH ENCRYPTION.
В следующем примере изменяется триггер, созданный ранее:
ALTER TRIGGER LowCredit ON PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p
INNER JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.VendorID = i.VendorID
IF @creditrating = 10
BEGIN
RAISERROR ('Низкая кредитоспособность покупателя.', 16, 1)
ROLLBACK TRANSACTION
END
GO
Удаление триггеров
Удаление триггеров осуществляется командой DROP TRIGGER.
DROP TRIGGER trigger_name
Trigger_name
Имя триггера.
В следующем примере удаляется триггер, созданный ранее:
DROP TRIGGER LowCredit
Контрольные вопросы по изученному материалу
Постарайтесь ответить на следующие вопросы:
1. Для чего служат ограничения целостности данных?
2. Какие типы ограничений целостности вы знаете?
3. В каких случаях применяются ограничения целостности данных таблиц?
4. При каких DML операциях могут вызываться триггеры?
5. Чем отличаются триггеры AFTER и INSTEAD OF?
6. Какие параметры конфигурации сервера влияют на рекурсию и вложенность триггеров?
7. Какие две временные таблицы используются для проверки результатов изменений данных и для установки условий срабатывания триггеров?