Триггеры INSTEAD OF реализуют новые способы, которые позволяют существенно расширить возможности баз данных.
НТриггеры INSTEAD OF, впервые реализованные Microsoft в версии SQL Server 2000, позволяют заменить то действие по модификации данных, которое пользовать адресует таблице или представлению. Подобно триггерам AFTER, триггеры INSTEAD OF пользуются доступом к таблицам Inserted и Deleted. Триггеры AFTER имеют дело с двумя образами строк: до проведения изменений и после того как все модификации будут выполнены. Триггеры INSTEAD OF также оперируют двумя образами строк: исходным и конечным. Но в отличие от триггеров AFTER в качестве конечного образа используется не тот, который приобрела строка после изменения, а такой, который она получила бы, если бы действие было выполнено, в то время как на самом деле это изменение не производилось. Имейте в виду, что действие пользователя не затронет базовую таблицу до тех пор, пока триггер INSTEAD OF не выполнит в ней свое действие. Триггеры INSTEAD OF предоставляют простые решения для таких проблем, с которыми было бы очень трудно справиться другими средствами. Фернандо Гуерреро, один из признанных корпорацией Microsoft профессионалов SQL Server, обладатель звания MVP, высказал идею, которую я развиваю в данной статье.
Запуск триггеров AFTER для отдельных строк при выполнении действий со многими строками
Прежде всего, рассмотрим, как следует применять триггеры INSTEAD OF для решения проблемы, которая возникает при использовании регулярных триггеров AFTER, поддерживающих вставку только отдельных строк. Одна из компаний, поставляющих клиентам корпорации Microsoft так называемые "решения независимых производителей", разработала триггер AFTER и зашифровала его так, что никто не может получить доступ к исходному коду. Теперь представьте себе ситуацию, когда необходимо выполнять вставку множества строк в принадлежащую вам таблицу, но сделать это не удается. Вы не в состоянии отключить или удалить триггеры, так как требуется, чтобы эти триггеры выполняли свои функции. Необходимо предоставить триггерам возможность поддерживать операции вставки множества строк. Чтобы увидеть, как триггер работает в такой ситуации, запустите код, приведенный в листинге 1. Он создает таблицу Т1 и триггер AFTER, который распечатывает данные введенной строки. Попробуйте вставить одну строку:
INSERT INTO T1 VALUES(10)
Получите следующий результат:
Who are you?
I`m 10
А теперь попытайтесь вставить сразу три строки:
INSERT INTO T1
SELECT 20 AS data_col
UNION ALL
SELECT 30 AS data_col
UNION ALL
SELECT 40 AS data_col
В этом случае вы получите сообщение об ошибке, извещающее о том, что подзапрос вернул более одного значения, а это недопустимо:
Server: Msg 512, Level 16, State 1, Procedure trg_T1_i_halo, Line 7Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is
used as an expression.
The statement has been terminated.
Если бы в таблице отсутствовал столбец, для которого определен тип данных IDENTITY, приведенный в листинге 2 триггер INSTEAD OF позволил бы решить данную проблему. Триггер просто организовал цикл по всем ключевым значениям в таблице вставляемых строк, Inserted, и последовательно вводит строки в базовую таблицу. При такой организации процесса триггер AFTER срабатывает отдельно для каждой строки. Заметьте, что триггер, показанный в листинге 2, обеспечивает одновременную вставку множества строк при наличии триггеров, производящих обработку построчно, если в таблице отсутствует столбец типа IDENTITY. Однако такое решение не подходит для примера с таблицей Т1, поскольку столбец key_col этой таблицы имеет тип данных IDENTITY. В столбце key_col таблицы Inserted во всех строках содержится нулевое значение, поскольку модификация никогда не достигает таблицы, когда срабатывает триггер и выполняется его код. Для решения этой проблемы можно использовать трюк, показанный в листинге 3. Этот код использует функцию IDENTITY(), которая генерирует значение ключа во временной таблице. Временная таблица наполняется данными из таблицы Inserted и вновь сформированными значениями ключей. Теперь триггер использует такую же логику, как и в предыдущем примере, однако новый цикл триггера выполняется не в таблице Inserted, а во временной таблице.
Теперь попробуйте произвести вставку нескольких строк:
INSERT INTO T1
SELECT 20 AS data_col
UNION ALL
SELECT 30 AS data_col
UNION ALL
SELECT 40 AS data_col
Получится следующее:
Who are you?
I`m 20
Who are you?
I`m 30
Who are you?
I`m 40
Как избежать нарушения ограничений при помощи таблицы журнала
Триггеры INSTEAD OF могут с успехом применяться и для сохранения целостности данных. Если для таблицы определен ряд ограничений, а вы вставляете целый набор строк, причем в одной строке одно из ограничений нарушено, то вся операция вставки завершится неудачей. Предпочтительнее было бы строки с нарушениями отправить в журнал, а корректные - ввести в таблицу. Эту задачу можно выполнить при помощи триггеров INSTEAD OF. Чтобы увидеть, как это работает, создайте таблицы OrderDetails и OrderDetailsLog, как показано в листинге 4. Обратите внимание на то, что в этом примере используются исходная таблица Orders из базы данных Northwind, когда создаются таблицы OrderDetails и OrderDetailsLog.
Теперь создадим триггер INSTEAD OF INSERT для таблицы OrderDetails, который будет записывать некорректные строки в таблицу журнала OrderDetailsLog. Обычно вместе с таким триггером создается и триггер INSTEAD OF UPDATE, обрабатывающий модификации строк. Однако в данном примере рассмотрен только триггер, отвечающий за вставку строк, что позволяет проиллюстрировать саму идею использования журнала. Триггер выполняет серию проверок, чтобы выяснить, выполняется ли ограничение первичного ключа, ограничение внешнего ключа и одно из условий, заданных для таблицы OrderDetails. Если в ходе проверок были выявлены некорректные строки, триггер записывает их в таблицу OrderDetailsLog. В заключение триггер вставляет все корректные строки в таблицу OrderDetailsLog. В листинге 5 показан код этого триггера.
Обратите внимание, что в некоторых строках могут быть нарушены несколько условий. В таких случаях триггер запишет в журнал эту строку столько раз, сколько ограничений было нарушено. Теперь попробуйте вставить в таблицу OrderDetails несколько строк, как показано в листинге 6. Данный триггер не генерирует никаких сообщений, если обнаружится нарушение ограничения, он только записывает строки, содержащие эти нарушения, в таблицу OrderDetailsLog. Если потребуется генерировать сообщение об ошибке при неудачной попытке вставить строку, воспользуйтесь операторами PRINT или RAISERROR, добавив их в код данного триггера.
Только в первой строке не нарушены никакие ограничения, поэтому в таблицу OrderDetails попадет лишь эта строка. В строках со второй по пятую имеется по одному нарушению какого-либо ограничения. Триггер запишет каждую из этих строк в журнал один раз, сопроводив запись описанием нарушения. В последней строке нарушены сразу три ограничения, поэтому триггер запишет эту строку в журнал трижды. После запуска сценария вставки строк взгляните на содержимое таблицы OrderDetailsLog, которое показано в таблице 1.
Применение триггеров INSTEAD OF в представлениях
Фернандо Гуерреро предложил следующую идею. Одним из важных достоинств, которыми обладают триггеры INSTEAD OF, является возможность их применения при работе с представлениями. Это позволяет поддерживать такие модификации представлений, которые были бы невозможны при других обстоятельствах, к примеру, дает возможность выполнять обновление представлений, использующих агрегирующие функции. Чтобы исследовать эти функции, запустите сценарий, приведенный влистинге 7. В соответствии с этим сценарием в таблицу OrderDetails, которая использовалась в предыдущих примерах, добавляется несколько строк. Теперь создайте представление VTotalOrders которое в соответствии с листингом 8 рассчитывает общее количество товаров для каждого заказа.
Предположим, пользователям разрешено вносить изменения через это представление. Обычно никому не позволяется модифицировать столбец с идентификатором заказа, OrderID. А как быть со столбцом TotalQty, в котором отображается вычисляемое суммарное количество товаров? В конце концов, суммарное количество товаров для конкретного заказа складывается из количества каждого из товаров, упомянутых в этом заказе. Можно придумать алгоритм, который будет распределять изменения, выполненные в столбце TotalQty, по всем товарам заказов в таблице OrderDetails. К примеру, можно распределять изменение суммарной величины числа закупленных товаров по каждой разновидности в соответствии с относительным количеством товаров этого типа в каждом заказе, участвующем в подсчете суммарного количества. В листинге 9 приведен триггер INSTEAD OF UPDATE, реализующий такой алгоритм распределения.
Формула, по которой для каждого товара рассчитывается новое значение заказанных единиц, проста. Она рассчитывает долю определенного товара от общего числа заказанных товаров для каждого конкретного заказа перед проведением модификации. После этого результат умножается на новое суммарное количество товаров для исходного заказа. Обратите внимание, что все затрагиваемые величины являются целыми числами. Это означает, что если бы мы предварительно не умножили значение столбца OD.Quantity на 1, в формуле использовались бы действия целочисленной арифметики. Таким образом, формула выполнит преобразование целых величин в десятичные, и в ходе вычислений не будет потеряна точность. Поскольку желательно хранить значения с максимальной точностью, необходимо произвести округление, чтобы избежать отсечения дробной части числа. Прежде чем выполнить обновление представления, взгляните на содержимое таблицы OrderDetails в таблице 2, и представление VTotalOrders, показанное в таблице 3
Теперь выполните следующее обновление представления:
UPDATE VTotalOrders
SET TotalQty = TotalQty * 2
Внимательно просмотрите содержимое таблицы и представления, они показаны соответственно в таблице 4 и таблице 5. Как видите, триггер точно выполняет свою функцию.
Итак, мы выяснили, каким образом с помощью триггеров INSTEAD OF можно решать разнообразные задачи: выполнять вставку множества строк, каждая из которых вызывает срабатывание триггера AFTER; загружать некорректные строки в таблицу журнала вместо того, чтобы отвергать всю модификацию; поддерживать обновления представления, использующего вычисление агрегирующих функций. Это всего лишь несколько примеров задач, которые можно решать при помощи триггеров INSTEAD OF.
Ицик Бен-Ган itzikb@hi-tech.co.il обладает сертификатами MCDBA, MCSE+I, MCSD, MCT и SQL Server MVP. Работает старшим преподавателем на курсах по SQL Server в колледже Hi-Tech в Израиле. Возглавляет израильскую группу пользователей SQL Server.