shop触发器.txt

简单邮电信息管理

CREATE TRIGGER insert_shop ON [dbo].[Shop_Info] 
FOR INSERT, UPDATE, DELETE 
AS
declare @i_id varchar
declare @i_size varchar
declare @i_num int
declare @i_date datetime
declare @s_id varchar
declare @s_size varchar
declare @s_quan int

select    @i_id=inserted.ID,
	@i_size=inserted.S_Size,
	@i_num=inserted.Num,
	@i_date=inserted.S_Date
from inserted

select	@s_id=Stor_Info.ID,
	@s_size=Stor_Info.S_Size,
	@s_quan=Stor_Info.Quantity
from Stor_Info
where Stor_Info.ID=@i_id
	and Stor_Info.S_Size=@i_size

if exists
(select Stor_Info.ID from Stor_Info
where Stor_Info.ID=@i_id
	and Stor_Info.S_Size=@i_size)

	update Stor_Info
		set Quantity=@i_num+@s_quan
	where Stor_Info.ID=@i_id
		and Stor_Info.S_Size=@i_size

else if not exists
(select Stor_Info.ID from Stor_Info
	where Stor_Info.ID=@i_id
		and Stor_Info.S_Size=@i_size)

insert into Stor_Info
(ID,S_Size,Quantity)
values (@i_id,@i_size,@i_num)