7. Описание хранимых процедур изменения данных

--Изменение количества товара

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Update_qunt_good') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Update_qunt_good

go

CREATE procedure Update_qunt_good

@name_good varchar(25),

@quantity int

as

UPDATE Goods set quantity_good=@quantity where name_good=@name_good

go

--exec Update_qunt_good 'Балда', 300

--select * from Goods

--Изменение отпускной цены товара

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Update_cost_good') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Update_cost_good

go

CREATE procedure Update_cost_good

@name_good varchar(25),

@cost int

as

UPDATE Goods set e_cost_good=@cost where name_good=@name_good

go

--exec Update_cost_good 'Балда', 456

--select * from Goods

--select * from History_cost

8. Описание хранимых процедур поиска

--Поиск товара по названию.

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

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Find_good') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Find_good

go

CREATE procedure Find_good

@name_good varchar(5)

as

SELECT P.name_group [Название группы],

G.name_good [Название товара],

G.quantity_good [Количество товара],

G.ed_izm [Единица измерения],

G.p_cost_good[Отпускная цена]

from Goods G JOIN Product_groups P on G.id_group=P.id_group

where name_good=@name_good

go

--Поиск клиента по коду.

--Вывод кода и скидки клиента.

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Find_client') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Find_client

go

CREATE procedure Find_client

@id_client int

as

SELECT id_client [Код клиента],

discount_client [Скидка]

from Clients

where id_client=@id_client

go

--Поиск всех клиентов, имеющих определенную скидку.

--Вывод кода и скидки клиента.

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Find_client2') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Find_client2

go

CREATE procedure Find_client2

@discount_client int

as

SELECT id_client [Код клиента],

discount_client [Скидка]

from Clients

where discount_client=@discount_client

go

--Поиск поставщика по названию.

--Вывод кода, название, адреса и телефона поставщика.

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Find_suppl') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Find_suppl

go

CREATE procedure Find_suppl

@name_supplier varchar(25)

as

SELECT id_supplier [Код поставщика],

name_supplier [Название поставщика],

address_supplier [Адрес поставщика],

telephon_supplier [Телефон поставщика]

from Suppliers

where name_supplier=@name_supplier

go

9. Описание триггеров

--Триггер, проверяющий что количество товаров добавляемое в заказ

--меньше или равно количеству товаров в Ассортименте

--и изменяющий количество товаров в Ассортименте при добавлении Заказа

CREATE trigger update_quantity

on Zakaz

after INSERT

as

if exists (select *

from inserted i

where i.quantity>(select G.quantity_good

from Goods G

where G.id_good=i.id_good

)

)

begin

PRINT 'Количество товара на складе меньше, чем в Заказе'

ROLLBACK TRANSACTION

RETURN

end

UPDATE G

SET G.quantity_good=G.quantity_good-Z.quantity

from inserted i

JOIN Zakaz Z on Z.id_zakaz=i.id_zakaz AND Z.id_good=i.id_good

JOIN Goods G on G.id_good=Z.id_good

--Удаление триггера

--drop trigger update_quantity

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

exec Add_zakaz 1,2,8

Теперь проверим, что если количество заказываемого товара не больше, чем есть на складе, оно вычтется из количества в Ассортименте:

exec Add_zakaz 5,25,2

exec Add_zakaz 6,10,1

При добавлении нескольких строк, триггер так же будет срабатывать:

INSERT INTO Zakaz(id_zakaz,id_good,quantity,cost)

select 7,12,1,207.00

union

select 7,22,1,37.00

INSERT INTO Zakaz(id_zakaz,id_good,quantity,cost)

select 7,13,100,207.00

union

select 7,23,100,37.00

--Триггер, изменяющий количество товаров в Ассортименте при добавлении Накладной

--и сохраняющий стоимость товара до новой поставки в Историю

CREATE trigger update_quantity2

on Consignment_note

after INSERT

as

if exists(select *

from inserted i

where i.cost<>(select G.p_cost_good

from Goods G

where G.id_good=i.id_good

)

)

begin

INSERT INTO History_cost(id_good,date_time_cost,e_cost_good,p_cost_good)

select i.id_good,getdate(),G.e_cost_good,G.p_cost_good

from inserted i, Goods G

where i.id_good=G.id_good

end

UPDATE G

SET G.quantity_good=G.quantity_good+C.quantity,

G.p_cost_good=C.cost

from inserted i

JOIN Consignment_note C on C.id_cons_note=i.id_cons_note

JOIN Goods G on G.id_good=C.id_good

Для проверки работы триггера добавим новую накладную и посмотрим, что измениться в таблицах Ассортимент и История стоимости:

exec Add_cons_note 8,1,3,100.00

--Триггер, сохраняющий стоимость товара до изменения его отпускной цены в Историю

CREATE trigger update_cost

on Goods

after UPDATE

as

if exists(select *

from deleted d

where d.e_cost_good<>(select G.e_cost_good

from Goods G

where G.id_good=d.id_good

)

)

INSERT INTO History_cost(id_good,date_time_cost,e_cost_good,p_cost_good)

select d.id_good,getdate(),d.e_cost_good,G.p_cost_good

from deleted d, Goods G

where d.id_good=G.id_good

--Удаление триггера

--drop trigger update_cost

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

exec Update_cost_good 'Эрудит', 330

Если мы вводим ту же цену, то в Историю ничего не добавляется:

exec Update_cost_good 'Балда', 230