Главная | Обратная связь | Поможем написать вашу работу!

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

Создайте хранимую процедуру с использованием курсора для вашей базы данных.

Триггеры

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

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

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

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

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

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

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

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

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

FOR INSERT | UPDATE | DELETE

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

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

CREATE TRIGGER upd_staff

ON k_staff FOR UPDATE

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

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

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

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

CREATE TRIGGER ins_prot

ON k_protokol FOR INSERT

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

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

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

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

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

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

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

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

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, вызывая тем самым ее собственный триггер. Такие цепочки вызовов триггеров могут быть и более длинными, главное – чтобы триггеры не конфликтовали друг с другом и не зацикливались.

⇐ Предыдущая 6 7 8 9 101112 13 14 15 Следующая ⇒