4.7Триггеры

SQL Server поддерживает только замещающие и завершающие триггеры. Предваряющие триггеры этой СУБД не поддерживаются. Таблица может иметь один или несколько завершающих триггеров вставки, обновления и удаления; завершающие триггеры не могут назначаться представлениям. Представление или таблица может иметь не более одного замещающего триггера на каждое из возможных действий – вставку, обновление или удаление.

В SQL Server триггеры могут производить откат транзакций, повлекших их запуск. Когда триггер выполняет команду ROLLBACK, все действия, произведенные транзакцией, которая привела к запуску триггера, отменяются. Если в тексте триггера за командой ROLLBACK следуют какие-либо инструкции, эти инструкции будут выполнены. Однако никакие инструкции, следующие за оператором, который вызвал запуск триггера, выполняться не будут.

Для триггеров вставки и обновления новые значения каждого столбца обрабатываемой таблицы хранятся в псевдотаблице под названием inserted. Если, например, в таблицы ARTIST добавляется новая строка, то псевдотаблица inserted будет содержать четыре столбца: Name, Nationality, BirthDate и DeceasedDate. Аналогичным образом, в случае обновления или удаления старые значения каждого столбца будут храниться в псевдотаблице с именем deleted. В приведенных далее примерах вы увидите, как используются эти таблицы.

Далее будут рассмотрены примеры, каждый из которых демонстрирует одно из возможных применений триггеров. Триггеры можно создавать, записывая их в текстовые файлы (первым оператором в таких файлах должен быть оператор CREATE TRIGGER или ALTER TRIGGER), Также их можно создать в программе Enterprise Manager, щелкнув правой кнопкой мыши на имени таблицы и выбрав All Tasks ► Manage Triggers (Все задачи ► Управление триггерами) или в Management Sdudio Express (рис. 4.6). В последнем случае появится окно, в которое вы можете ввести текст триггера. Однако вы не сможете сохранить триггер в базе данных, если в нем будут синтаксические ошибки.

Рис. 4.6 Создание триггера в Management Sdudio Express

4.7.1 Триггер, реализующий бизнес-правило. Галерея View Ridge ведет список «проблемных» клиентов – тех, кто не оплатил вовремя покупку или представил какие-то другие проблемы для галереи. Когда в базу данных вводится покупатель, фигурирующий в списке таких клиентов, менеджер галереи хотел бы знать об этом.

Для реализации этой политики галерея создала таблицу PROBLEM_ACCOUNT: в нее записывается информация о «проблемных» клиентах. При вставке или обновлении данных о клиенте триггер проверяет, не фигурирует ли клиент в таблице PROBLEM_ACCOUNT. Если да, то выполняется откат вставки или обновления и выводится соответствующее сообщение.

Триггер CheckForProblemAccount в листинге 6 реализует эту политику. Хотя в тексте триггера не указано явно, что он является завершающим, он именно завершающий, поскольку в SQL Server не поддерживаются предваряющие триггеры, а в заголовке триггера отсутствуют ключевые слова INSTEAD OF, которые обозначают замещающий триггер.

Листинг 6. Триггер CheckForProblemAccount

CREATE TRIGGER [CheckForProblemAccount] ON [dbo].[CUSTOMER]

FOR INSERT, UPDATE

AS

/* Пытаемся соединить данные из inserted со строкой в таблице PROBLEM_ACCOUNT */

SELECT *

FROM inserted I JOIN PROBLEM_ACCOUNT P

ON I.Name = P.Name

AND I.AreaCode = P.AreaCode

AND I.PhoneNumber = P.PhoneNumber

/*В следующем операторе необходимо проверить @@rowCount. Если результат соединения не пуст, выполняем откат транзакции и выдаем сообщение. */

If @@rowCount > 0

BEGIN

ROLLBACK

Print 'Клиент принадлежит к числу "проблемных".'

Print 'Вставка и обновление не разрешены.'

END

Триггер CheckForProblemAccount пытается соединить новые значения в псевдотаблице inserted по очереди со всеми строками таблицы PROBLEM_ACCOUNT. Если соединение возвращает какие-либо результаты, значит, данный клиент присутствует в таблице PROBLEM_ACCOUNT. В этом случае следует выполнить откат транзакции и вывести предупреждение.

Данный триггер использует системную функцию @@rowCount, которая возвращает количество строк, обработанных предыдущим оператором T/SQL. Значение этой функции необходимо проверять сразу после выполнения оператора или сохранять в переменной для последующей проверки. Здесь триггер выполняет эту проверку сразу.

Если @@rowCount равняется нулю, то данный клиент отсутствует в таблице PROBLEM_ACCOUNT и ничего делать не требуется. Если же @@rowCount имеет положительное значение, значит, информация о данном клиенте содержится в таблице PROBLEM_ACCOUNT, поэтому транзакцию необходимо отменить, а пользователю выдать предупреждающее сообщение.

Между прочим, в предыдущих двух процедурах вместо подсчета строк в переменной @rowCount можно было бы использовать функцию @@rowCount. В данном случае сделано по-другому, чтобы была возможность изучить альтернативные способы получения информации о количестве строк.

Чтобы проверить, что триггер работает корректно, в таблицу PROBLEM_ACCOUNT была добавлена строка со значениями 'Nicole Not Pay', '213', '555-1234'. После этого был выполнен оператор INSERT, показанный на рис. 4.7. Как вы можете видеть, пользователю было выдано соответствующее сообщение. Была также проверена таблица CUSTOMER, чтобы убедиться, что вставка не была выполнена (эти действия не показаны).

Рис. 4.7. Тестирование триггера CheckForProblemAccount

Использование таблицы допустимых (или недопустимых) значений обеспечивает большую гибкость и динамичность, чем указание этих значений в ограничениях CHECK. Рассмотрим, например, ограничение CHECK, определяющее допустимые значения столбца Nationality в таблице ARTIST. Если менеджер галереи хочет расширить географию художников, с произведениями которых работает галерея, ему придется изменить ограничение с помощью оператора ALTER TABLE. В реальности для этого менеджеру пришлось бы нанимать консультанта.

Более удачный подход — свести все допустимые значения столбца Nationality в таблицу. Такая таблица могла бы называться, например, ALLOWED_NATIONALITY (допустимая национальность). Затем можно написать триггер, подобный изображенному в листинге 6, который при вставке и обновлении ограничивал бы возможные значения столбца Nationality множеством значений в таблице ALLOWED_ NATIONALITY. Если владелец галереи захочет изменить список национальностей, ему достаточно будет добавить или удалить строки из таблицы ALLOWED_NATIONALITY.

4.7.2 Триггер, присваивающий значения по умолчанию. Триггеры можно использовать для присвоения начальных значений, расчет которых слишком сложен, чтобы его можно было выполнить с помощью ограничения DEFAULT в определении столбца. Например, согласно ценовой политике галереи View Ridge, запрашиваемая цена произведения, которая устанавливается по умолчанию, зависит от того, появлялось ли это произведение в галерее в прошлом. Если нет, она устанавливается равной удвоенной стоимости приобретения. Если произведение уже появлялось в галерее прежде, запрашиваемая цена устанавливается равной большему из двух значений – удвоенной стоимости приобретения или стоимости приобретения плюс средняя чистая прибыль от продажи произведения в прошлом.

Завершающий триггер, показанный в листинге 7, реализует эту ценовую политику. Сначала из таблицы inserted получаются значения WorkID и AcquisitionPrice. Затем выполняется оператор SELECT FROM dbo.TRANS для подсчета количества строк с данным значением WorkID. Переменная @countPriorRows устанавливается равной @@rowCount минус единица, поскольку данный триггер является завершающим, и новая строка будет уже добавлена в базу данных. Следовательно, количество удовлетворяющих условию строк в таблице TRANS, которое было в базе данных до выполнения вставки, равно (@@rowCount - 1).

Листинг 7. Триггер SetAskingPrice

CREATE TRIGGER [SetAskingPrice] ON [dbo].[TRANS]

FOR INSERT

AS

DECLARE

@WorkID as int,

@TransID as int,

@aqPrice as numeric(8, 2),

@SumNetPrice numeric(8, 2),

@countPriorRows as int,

@newPrice as numeric(8, 2)

/* Получаем новые значения WorkID и AcquisitionPrice */

SELECT @WorkID = WorkID, @aqPrice = AcquisitionPrice, @TransID = TransactionID

FROM inserted

/* Проверяем, не появлялось ли произведение в галерее ранее */

SELECT *

FROM dbo.TRANS T

WHERE Т.WorkID = @WorkID

/* Это завершающий триггер, поэтому число строк будет включать только что вставленную строку. Вычитаем единицу, чтобы учесть это. */

Set @countPriorRows = @rowCount - 1

/* Устанавливаем newPrice и при необходимости корректируем ее значение*/

Set @newPrice = 2*@aqPrice

If @countPriorRows > 0

BEGIN

/* Функция AVG не подходит, поскольку в делителе будет учтена текущая строка. Используем @@rowCount - 1 из предыдущих вычислений. */

SELECT @sumNetPrice = SUM(SalesPrice)

FROM dbo.TRANS T

WHERE T.WorkID = @WorkID

GROUP BY T.WorkID

/* Если необходимо, корректируем newPrice */

If @newPrice < (@sumNetPrice/@countPriorRows) + @aqPrice

Set @newPrice = (@sumNetPrice/@countPriorRows) + @aqPrice

END

/* Теперь записываем в таблицу TRANS новое значение AskingPrice */

UPDATE dbo.TRANS

SET AskingPrice = @newPrice

WHERE TransactionID = @TransID

Далее переменная @newPrice устанавливается равной удвоенному значению AcquisitionPrice; при необходимости ее значение будет скорректировано ниже. Если @countPriorRows больше нуля, то в таблице TRANS до выполнения вставки уже были записи о транзакциях с данным произведением, и с помощью представления ArtistWorkNet вычисляется суммарная чистая прибыль от продажи этого произведения. Встроенную функцию усреднения использовать нельзя, поскольку она будет вычислять среднее на базе значения @@rowCount, а не @countPriorRows. Затем текущее значение @newPrice сравнивается с суммой средней чистой прибыли и стоимости приобретения. Если оно оказывается меньше, переменная @newPrice устанавливается равной сумме средней чистой прибыли и стоимости приобретения. Наконец, только что вставленная строка обновляется, в процессе чего в столбец AskingPrice записывается вычисленное значение @newPrice.

Для тестирования этого триггера необходимо проверить следующие три случая:

• произведение не появлялось в галерее ранее;

• произведение появлялось в галерее прежде, но сумма средней чистой прибыли и стоимости приобретения меньше или равна удвоенной стоимости приобретения;

• произведение появлялось в галерее прежде, но сумма средней чистой прибыли и стоимости приобретения превышает удвоенную стоимость приобретения.

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

4.7.3 Триггер, реализующий процедуру обеспечения ссылочной целостности. Согласно рис. 4.2, таблица WORK в базе данных View Ridge имеет обязательного потомка в виде таблицы TRANSACTION. Это означает, что всякий раз, когда в таблицу WORK вставляется строка, необходимо вставить соответствующую ей строку в таблицу TRANSACTION.

Реализовать такие ограничения ссылочной целостности непросто. В листингах 8, а и б, показана пара триггеров, создающих строку в таблице TRANS при вставке в таблицу WORK и удаляющих ее, когда (и если) приложение самостоятельно вставляет строку в таблице TRANS после создания строки в таблице WORK. Триггер в листинге 8, а, создает строку в таблице TRANS по умолчанию. Сначала он получает новое значение WorkID из псевдотаблицы inserted, а затем проверяет, нет ли доступной строки в таблице TRANS. Такой строки быть не должно, поскольку транзакция, создавшая строку в таблице WORK, еще не получила шанса вставить соответствующую строку в таблицу TRANS. После этого, если @@rowCount равняется нулю, триггер создает в таблице TRANS строку по умолчанию.

Листинг 8, а. Триггеры для реализации ограничения обязательного потомка: триггер EnforceTransChild

CREATE TRIGGER EnforceTransChild ON [dbo].[WORK]

FOR INSERT

AS

DECLARE @newWorkID as int

/* Получаем новый WorkID */

SELECT @newWorkID = WorkID

FROM inserted

/* Проверяем, имеется ли доступная строка в таблице TRANS */

SELECT *

FROM dbo.TRANS

WHERE WorkID=@newWorkID AND CustomerID IS NULL

/* Если подходящей строки не найдено, вставляем строку */

If @@rowCount = 0

INSERT INTO dbo.TRANS (DateAcquired, WorkID) VALUES (GetDate(), @newWorkID)

Триггер в листинге 8, б, запускается при вставке в таблицу TRANS. Назначение данного триггера – удалять из этой таблицы созданную по умолчанию строку, если приложение собирается создать такую строку самостоятельно. Если приложение не запросит вставку такой строки, триггер запущен не будет, и данные о транзакции для нового произведения будут записаны в строку, созданную предыдущим триггером.

Листинг 8, б. Триггеры для реализации ограничения обязательного потомка: триггер RemoveDupTrans

CREATE TRIGGER RemoveDupTrans ON [dbo].[TRANS]

FOR INSERT

AS

DECLARE @WorkID as int, @TransID as int

SELECT @WorkID = WorkID, @TransID = TransactionID

FROM inserted

/* Удаляем прочие доступные строки из таблицы TRANS */

DELETE FROM dbo.TRANS

WHERE WorkID =@WorkID AND CustomerID IS NULL AND TransactionID <> @TransID