Триггеры. Триггеры – это хранимые процедуры специального вида, которые автоматически выполняются при изменении таблицы с помощью операторов INSERT

Триггеры – это хранимые процедуры специального вида, которые автоматически выполняются при изменении таблицы с помощью операторов INSERT, UPDATE и DELETE. Триггер создается для определенной таблицы, но может использовать данные других таблиц и объекты других баз данных.

Существует 3 типа триггеров: INSERT, UPDATE и DELETE. Правила работы с триггерами следующие:

- триггеры запускаются только после выполнения вызвавшего их оператора;

- если при выполнении оператора возникает нарушение какого-либо ограничения или другая ошибка, триггер не срабатывает;

- триггер и вызвавший его оператор образует транзакцию. Если нужно из триггера отменить вызвавшую его операцию, следует выполнить откат транзакции ROLLBACK;

- триггер срабатывает один раз для каждого оператора, независимо от количества изменяемых им записей.

Краткий формат триггера (более подробно смотрите в Book Online):

CREATE TRIGGER имя_триггера

ON имя_таблицы

FOR INSERT | UPDATE | DELETE

AS

Код_триггера

Рассмотрим элементарный пример: при обновлении таблицы “Сотрудники” печатается сообщение (не делайте подобных триггеров в качестве задания для самостоятельной работы!).

CREATE TRIGGER upd_staff

ON k_staff FOR UPDATE

AS

PRINT "Обновили таблицу Сотрудники"

После создания триггера нужно протестировать его, выполнив команду UPDATE для таблицы Сотрудники.

При добавлении строки в таблицу ее копия помещается во временную таблицу с именем Inserted, при удалении – с именем Deleted. При обновлении старая версия строки помещается во временную таблицу с именем Deleted, новая – с именем Inserted. Эти временные таблицы часто используются в триггерах.

Рассмотрим пример триггера вставки, который вызывается при выполнении команды INSERT в таблице протоколов счетов. При добавлении новой позиции в счете нам нужно заново пересчитать его общую сумму.

CREATE TRIGGER ins_prot

ON k_protokol FOR INSERT

AS

DECLARE @s_new NUMERIC(9,2),

@kolvo NUMERIC(6),

@bill_num NUMERIC(6)

SELECT @kolvo=kolvo FROM Inserted

IF @kolvo>0

BEGIN

SELECT @s_new=p.price_sum,

@bill_num=bill_num

FROM k_price p, Inserted i

WHERE p.price_num=i.price_num

IF @s_new!=0

UPDATE k_bill

SET bill_sum=bill_sum+@s_new*@kolvo

WHERE k_bill.bill_num=@bill_num

END

Для тестирования триггера следует выполнить команду добавления, например:

Выберем информацию о счете №1:

SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1

Получим:

bill_num bill_sum

-----------------

1 1000

Теперь добавим строку в протокол этого счета:

INSERT INTO k_protokol

(price_num, bill_num, kolvo, price_sum)

VALUES(5, 1, 1, 5000);

Снова выберем информацию о счете №1:

SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1

Получим:

bill_num bill_sum

-----------------

1 6000

Как видим, сумма счета увеличилась на стоимость выбранного товара.

Рассмотрим пример триггера удаления, который вызывается при выполнении команды DELETE в таблице протоколов счетов. При удалении позиции в счете нам нужно пересчитать его сумму. Здесь возникает следующая проблема – если в команде DELETE было удалено сразу несколько строк, трудно будет их обработать. Поэтому сначала мы выполняем проверку: сколько строк было удалено. Эта информация хранится в глобальной переменной @@ROWCOUNT. Если количество удаленных строк больше 1, выводим сообщение об ошибке и отменяем команду DELETE. В остальном этот триггер похож на предыдущий.

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

DECLARE @s_old NUMERIC(9,2),

@kolvo NUMERIC(6),

@bill_num NUMERIC(6)

IF @@ROWCOUNT>1

BEGIN

RAISERROR

("Нельзя удалять более 1 строки за раз!",16,1)

ROLLBACK TRAN

END

ELSE

BEGIN

SELECT @kolvo=kolvo FROM Deleted

IF @kolvo>0

BEGIN

SELECT @s_old=p.price_sum,

@bill_num=bill_num

FROM k_price p, Deleted d

WHERE p.price_num=d.price_num

IF @s_old!=0

UPDATE k_bill

SET bill_sum=bill_sum-@s_old*@kolvo

WHERE k_bill.bill_num=@bill_num

END

END

Ту же задачу можно решить другим образом. Просто пересчитаем суммы для всех счетов. Если в таблице Deleted есть строки протокола для какого-то счета, его сумма будет уменьшена. Этот триггер получится гораздо короче, но он неэффективен, так как обрабатывает все счета.

Обратите внимание, что в команде UPDATE используется связанный подзапрос.

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

UPDATE k_bill SET bill_sum = bill_sum -

(SELECT SUM(price_sum*kolvo)

FROM Deleted d

WHERE d.bill_num=k_bill.bill_num)

Выполним эту команду. Все нормально, ошибок нет.

Теперь попробуем удалить какую-нибудь строку из протокола счетов. Выдается ошибка

Cannot insert the value NULL into column 'bill_sum'

В чем же дело? Дело в том, что функция SUM вместо ожидаемых числовых значений 0 возвратила NULL-значения для тех счетов, информации о которых нет в таблице Deleted. Чтобы преобразовать ненужные NULL в числовые нули, удобно использовать функцию ISNULL. Она имеет формат

ISNULL(выражение, значение_вместо_NULL)

В том случае, если выражение не равно NULL, функция возвращает выражение. Если равно NULL, то значение_вместо_NULL. Триггер примет вид:

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

UPDATE k_bill SET bill_sum = bill_sum -

ISNULL((SELECT SUM(price_sum*kolvo)

FROM Deleted d

WHERE d.bill_num=k_bill.bill_num),0)

Рассмотрим еще один пример. В таблице платежей мы (на свою голову) установили составной первичный ключ: "номер_счета, номер_платежа", причем номер_платежа должен быть уникальным только в пределах его счета. Т.о., мы не могли для заполнения этого поля использовать свойство IDENTITY (по умолчанию в этом поле мы назначили 0). Попробуем создать триггер для поиска максимального кода платежа по данному счету и формирования нового номера платежа. Все команды в этом триггере вам уже знакомы.

CREATE TRIGGER ins_pay

ON k_payment FOR INSERT

AS

DECLARE @n NUMERIC(6),

@bill NUMERIC(6)

SELECT @bill=bill_num FROM Inserted

SELECT @n=ISNULL(MAX(p.payment_num), 0)

FROM k_payment p, Inserted i

WHERE p.bill_num=i.bill_num

UPDATE k_payment SET payment_num=@n+1

WHERE bill_num=@bill and payment_num=0

Триггеры также удобно использовать для поддержания ссылочной целостности. Мы уже использовали декларативную ссылочную целостность с помощью внешних ключей, но она имеет исключительно запретительный характер. На самом же деле политика ссылочной целостности может быть пяти видов:

Ø IGNOGE – игнорировать,

Ø RESTRICT – запрещать,

Ø CASCADE – каскадная обработка,

Ø SET DEFAULT – назначать значения по умолчанию,

Ø SET NULL – назначать NULL-значения.

Политика IGNORE означает, что мы не предусматриваем никаких проверок и ограничений.

Политика RESTRICT действует, когда мы применяем ограничения внешних ключей.

При использовании политики CASCADE мы должны предусмотреть собственную программную обработку, т.е. при изменении родительских таблиц вносить изменения в дочерние таблицы программным образом.

Политика SET DEFAULT состоит в том, что при изменении данных в родительских таблицах дочерним назначаются значения по умолчанию. Например, при удалении отдела мы можем записать его сотрудников в некоторый другой отдел, который мы считаем отделом по умолчанию.

Политика SET NULL похожа на предыдущую, только мы назначаем NULL-значения.

Рассмотрим следующий пример. Пусть при удалении счета мы хотим удалять все строки его протокола. Пока у нас на этот случай действует внешний ключ, который запрещает удалять счет, для которого есть протокол.

Уберем этот внешний ключ:

ALTER TABLE k_protokol DROP CONSTRAINT fk_protokol_bill_num

Создадим триггер:

CREATE TRIGGER del_bill

ON k_bill FOR DELETE

AS

DELETE FROM k_protokol WHERE bill_num IN

(SELECT bill_num FROM Deleted d)

Протестируем триггер. Распечатаем сначала протокол счета с номером 5.

SELECT * FROM k_protokol WHERE bill_num=5

price_num bill_num kolvo price_sum

--------- -------- -------- -----------

1 5 1 1000.00

2 5 10 100.00

(2 row(s) affected)

Теперь удалим этот счет.

DELETE FROM k_bill WHERE bill_num=5

Снова распечатаем протокол этого счета.

SELECT * FROM k_protokol WHERE bill_num=5

price_num bill_num kolvo price_sum

--------- -------- -------- -----------

(0 row(s) affected)

Как видим, строки протокола тоже удалены.

Заметим, что этот триггер удаляет строки из таблицы k_protokol, вызывая тем самым ее собственный триггер. Такие цепочки вызовов триггеров могут быть и более длинными, главное – чтобы триггеры не конфликтовали друг с другом и не зацикливались.

Задание для индивидуальной работы 10

Создайте несколько (не менее 2) триггеров для вашей базы данных. Например, с помощью триггеров вы можете реализовать каскадную политику ссылочной целостности.

Приложение. Некоторые типичные ошибки

При отладке программ неизбежно обнаруживаются разнообразные ошибки. Рассмотрим некоторые типичные ситуации при работе в Query Analyzer.

Команда:

SELCT * FROM k_bill

Ошибка:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '*'.

Объяснение:

Синтаксическая ошибка, пропущена буква в слове SELECT.

Команда:

INSERT INTO k_firm (firm_name, firm_addr)

VALUES(10, 'Сигма', 'Киев');

Ошибка:

Server: Msg 110, Level 15, State 2, Line 1

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Объяснение:

В команде вставки в списке полей перечислены два поля, а в списке значений – три значения.

Команда:

INSERT INTO k_firm (firm_num, firm_name, firm_addr)

VALUES(10, 'Сигма', 'Киев');

Ошибка:

Server: Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'k_firm' when IDENTITY_INSERT is set to OFF.

Объяснение:

Нельзя указывать явное значение для поля, у которого установлено свойство IDENTITY, т.е., для поля firm_num.

Команда:

INSERT INTO k_staff

(staff_name, dept_num, staff_hiredate, staff_post)

VALUES('Смит', 4, GETDATE(), 'Менеджер');

Ошибка:

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'fk_staff_dept_num'. The conflict occurred in database 'kontora', table 'k_dept', column 'dept_num'.

The statement has been terminated.

Объяснение:

Нарушено ограничение внешнего ключа: мы пытаемся вставить ссылку на несуществующий отдел с номером 4.

Команда:

DELETE FROM k_contract WHERE contract_num=1

Ошибка:

Server: Msg 547, Level 16, State 1, Line 1

DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_bill_contract_num'. The conflict occurred in database 'kontora', table 'k_bill', column 'contract_num'.

The statement has been terminated.

Объяснение:

Нарушено ограничение внешнего ключа: мы пытаемся удалить договор с номером 1, а к этому договору привязаны счета в таблице k_bill.

Команда:

SELECT * FROM k_contract

WHERE contract_date BETWEEN '01/03/2006' AND '31/03/2006'

Ошибка:

Server: Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Объяснение:

По умолчанию в SQL Server даты записываются в американском формате: месяц/день/год.

Команда:

SELECT price_name, MIN(price_sum) FROM k_price

Ошибка:

Server: Msg 8118, Level 16, State 1, Line 1

Column 'k_price.price_name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Объяснение:

Если используются агрегирующие функции без группировки, в списке полей могут присутствовать только агрегирующие функции.

Команда:

SELECT contract_num, contract_date, bill_num, bill_date

FROM k_bill, k_contract

WHERE k_bill.contract_num=k_contract.contract_num

Ошибка:

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'contract_num'.

Объяснение:

Если в нескольких таблицах, используемых в запросе, есть поля с одинаковыми названиями, то для обращения к таким полям следует использовать синтаксис имя_таблицы.имя_поля или псевдоним.имя_поля.

Команда:

SELECT contract_num, contract_date FROM k_contract

WHERE contract_num =

(SELECT contract_num FROM k_bill

WHERE bill_date

BETWEEN '01/01/2006' AND '12/31/2006'

AND k_contract.contract_num=k_bill.contract_num)

Ошибка:

Server: Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=, <, <=, >, >= or when the subquery is used as an expression.

Объяснение:

Нельзя использовать арифметические операции сравнения с подзапросом, если подзапрос возвращает несколько строк. Следует использовать ключевые слова ALL или ANY.

Команда:

INSERT INTO k_dept (dept_short_name, dept_full_name)

VALUES('Служба безопасности', 'Отдел №1');

Ошибка:

Server: Msg 8152, Level 16, State 9, Line 1

String or binary data would be truncated.

The statement has been terminated.

Объяснение:

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

Литература

1. Крёнке Д. Теория и практика построения баз данных // СПб: Питер, 2003.

2. Пирогов В. MS SQL Server 2000: управление и программирование // СПб: БХВ-Петербург, 2005.

3. Фронковяк Дж., Гарсиа М., Уолен Э. Руководство администратора Microsoft SQL Server 7.0. // М: Русская редакция, 2000.

4. Тихомиров Ю. Microsoft SQL Server 7.0 в подлиннике // СПб: БХВ-Петербург, 1999.

Дата добавления: 2015-05-09