Триггеры INSERT можно использовать для изменения или даже удаления вставленной записи. Хорошим примером использования этого триггера является предотвращение добавления определенных типов записей, таких как данные клиентов с лимитом кредитования больше 10000 долларов. К качестве еще одного примера можно привести добавление или изменение запрещенных данных во вставляемой записи (к примеру, изменение даты создания записи или имени пользователя, вставляющего эту запись.
Триггеры INSERT запускаются (и выполняются) при каждой попытке создать новую запись в таблице с помощью команды INSERT. При попытке пользователя вставить новую запись в таблицу, SQL Server копирует эту запись в таблицу триггеров базы данных и в специальную таблицу, которая хранится в памяти и имеет имя inserted. Это означает, что ваша новая запись существует в двух таблицах — таблице триггеров и таблице inserted. Запись в таблице inserted должна полностью соответствовать записи в таблице триггеров.
Таблицу inserted удобно использовать, когда требуется выполнить каскадные изменения в других таблицах базы данных. Предположим, что существует база данных, содержащая информацию о клиентах, заказах и товарах. Каждый раз при выполнении заказа клиента вам нужно вычитать эти товары в учете складских запасов (т.е. в таблице товаров), чтобы поддерживать правильный баланс. Существует два способа решения этой задачи. Первый заключается в хранении данных о проданных клиенту товарах во временной переменной (или переменной в памяти) и обновлении таблицы товаров с помощью второй инструкции UPDATE. Однако такой метод требует написания дополнительного кода, который может замедлить работу системы, и это нельзя считать идеальным решением. Второй способ состоит в использовании логической таблицы inserted. Требуемое значение хранится в двух местах— таблицах триггеров и inserted, так что вы можете извлечь значение из таблицы inserted и использовать его. Это означает, что вы можете вписать в триггер на вставку код автоматического вычитания данных из таблицы товаров на основе значения в таблице inserted. Он может выглядеть примерно так.
UPDATE p
SET p.instock = {p.instock - i.qty)
FROM Products p JOIN inserted i ON p.prodid = i.prodid
Чтобы создать такой триггер и посмотреть, как он работает, вы должны выполнить несколько предварительных условий. Во-первых, вам нужна база данных Sales, созданная в работе 5. Во-вторых, вам нужно заполнить таблицу некоторыми значениями.
1. Откройте SQL Server Management Studio и выполните регистрацию с использованием аутентификации Windows или SQL Server.
2. Вставим в таблицу несколько записей о клиентах для продажи им товаров. Откройте новое окно запросов SQL Server и выполните следующий код заполнения таблицы клиентов информацией. (Для проверки значений запустите запрос SELECT * FROM customers).
3. Теперь вставим несколько записей о товарах для продажи. Чтобы заполнить таблицу товаров, введите и выполните следующий код.
INSERT Products
VALUES ('Giant Wheel of Brie', 200)
INSERT Products
VALUES ('Wool Blankers', 545)
INSERT Products
VALUES ('Espresso Beans', 1527)
INSERT Products
VALUES ('Notepads', 2098)
4. Закройте окно запросов.
Итак, мы заполнили данными таблицы в базе данных Sales и готовы создать триггер, который будет автоматически обновлять столбец instock таблицы Products (товары на складе) на основе данных о товаре, проданном клиенту. Мы создадим триггер INSERT в таблице Orders (заказы на продажу), поскольку при продаже товара клиенту вы вставляете новую запись в таблицу Orders.
1. Откройте SQL Server Management Studio и раскройте папки сервера Databases=> Sales=>Tables=> dbo.Orders.
2. Щелкните правой кнопкой мыши на папке Triggers и выберите команду New Trigger.
3. В строке CREATE TRIGGER введите имя триггера (InvUpdate), в строке ON введите имя таблицы (dbo.Orders).
4. В строке AFTER оставьте INSERT (удалите Update и Delete).
5. В поле (--Insert statements for trigger here) введите следующий код триггера (рис. 1).
UPDATE p
SET p.instock = (p.instock - i.qty) FROM Products p JOIN inserted i ON p.prodid = i.prodid
6. Для создания триггера щелкните на кнопке Execute.
Теперь вы можете протестировать триггер INSERT. В следующей последовательности операций мы создадим в таблице Orders новую запись (имитируя таким образом заказ от клиента) для запуска триггера. Он должен уменьшить значение в столбце instock (остатки на складе) таблицы Products.
1. Откройте новый запрос SQL Server и выполните следующий код проверки значения instock для элемента 7 (оно должно быть равно 200) (рис. 2):
