Триггеры

Задача – создать триггеры для команд insert, delete, update, накладывающие ограничения целостности на таблицы Отгрузка и Списание. Это:

1) Остаток товара из данной партии не должен превышать списания или отгрузки,

2) При удалении записи остаток восстанавливается до предыдущего значения, при добавлении – увеличивается на добавляемую величину.

3) Дата не должна быть позже текущей

Триггер – это набор команд Transact-SQL, выполняемых автоматически при осуществлении тех или иных модификаций в таблице. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, удаление или изменение данных. Разработчик реализует в триггере необходимые проверки и изменения данных в других таблицах базы данных.

Триггеры бывают трех видов – на insert, на delete или на update. Рассмотрим все три.

create trigger s_ins

on spisanie for insert

as

if (select kol_spis from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_spis from inserted)

commit tran

return

end

else

begin

raiserror('Списание превышает остаток!!!',1,11)

rollback tran

end

return

В данном триггере идет проверка (1) условия. Если условие выполняется, из остатка поставки вычитается количество списания, и транзакция завершается. Запись о списании внесена в БД. В обратном случае триггер выдает сообщение об ошибке и отменяет ввод данных.

Выполним этот триггер.

Теперь попробуем ввести данные, нарушающие наше условие:

insert spisanie(id_part,data_spis,kol_spis,prich_spis) values(4,'02.16.2008',500,'Протухло')

Аналогичный триггер на таблицу «Отгрузка»:

create trigger o_ins

on otgruzka for insert

as

if (select kol_otgr from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_otgr from inserted)

commit tran

return

end

else

begin

raiserror('Нужное количество отсутствует на складе!!!',1,11)

rollback tran

end

return

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

create trigger s_del

on spisanie for delete

as

update postavka set ost_post=ost_post+(select kol_spis from deleted)

commit tran

return

create trigger o_del

on otgruzka for delete

as

update postavka set ost_post=ost_post+(select kol_otgr from deleted)

commit tran

return

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

create trigger s_update

on spisanie for update

as

if update (id_part)

begin if (select kol_spis from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_spis from inserted)

update postavka set ost_post=ost_post+(select kol_spis from deleted)

commit tran

end

else

begin

raiserror('Списание превышает остаток!!!',1,11)

rollback tran

end

end

if update (data_spis)

begin if (select data_spis from inserted) >= getdate()

begin

raiserror('Введена неправильная дата!!!',1,11)

rollback tran

end

end

if update (kol_spis)

if (select kol_spis from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_spis from inserted)

update postavka set ost_post=ost_post+(select kol_spis from deleted)

commit tran

end

else

begin

raiserror('Списание превышает остаток!!!',1,11)

rollback tran

end

return

Выполним команду и проверим триггер. Предположим, мы изменяем дату на большую, чем текущая.

Триггер для «Отгрузка»:

create trigger o_update

on otgruzka for update

as

if update (id_part)

begin if (select kol_otgr from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_otgr from inserted)

update postavka set ost_post=ost_post+(select kol_otgr from deleted)

commit tran

end

else

begin

raiserror('Отгрузка превышает остаток!!!',1,11)

rollback tran

end

end

if update (data_otgr)

begin if (select data_spis from inserted) >= getdate()

begin

raiserror('Введена неправильная дата!!!',1,11)

rollback tran

end

end

if update (kol_otgr)

if (select kol_otgr from inserted) <= (select ost_post from postavka join inserted on (postavka.id_part=inserted.id_part))

begin

update postavka set ost_post=ost_post-(select kol_otgr from inserted)

update postavka set ost_post=ost_post+(select kol_otgr from deleted)

commit tran

end

else

begin

raiserror('Отгрузка превышает остаток!!!',1,11)

rollback tran

end

if update (cena_prod)

begin

if (select cena_post from (postavka join inserted on (postavka.id_post=inserted.id_post))<= cena_otgr

commit tran

else begin

raiserror('Отгрузка превышает остаток!!!',1,11)

rollback tran

end

Дата добавления: 2015-07-08