触发器求助:多行INSERT,delete和UPDATE时如何处理?
用游标对INSERTED和DELETED表一行一行处理?
下面是我写的,只能用一行处理?
如何改才成对多行的情况处理?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [instock_insert] ON [dbo].[instock]
FOR INSERT
AS
--定义临时变量
declare
@temp_depart_id char(12),--产品所属部门
@temp_product_id char(12),--产品型号
@temp_product_tiepian_id tinyint,--贴片方式
@temp_product_level_id tinyint,--产品等级
@temp_in_stock_date datetime, --入库时间
@temp_in_stock_quantity bigint, --入库数量
@temp_stock_quantity bigint, --库存数量
--与工令有关的数据
@temp_process_id char(12),
@temp_process_item char(4)
--读取INSERT表中的数据
select
@temp_depart_id=produce_units_id,
@temp_product_id=i.product_id,
@temp_product_tiepian_id=i.product_tiepian_id,
@temp_product_level_id=i.product_level_id,
@temp_in_stock_date=i.in_stock_date,
@temp_in_stock_quantity=i.in_stock_quantity
from
inserted i
--处理INSERTED表数据
--读取库存表相关信息,如无此产品信息则INSERT,有则UPDATE
select
@temp_stock_quantity=s.stock_quantity
from
stock s
where
s.depart_id=@temp_depart_id and
s.product_id=@temp_product_id and
s.product_tiepian_id=@temp_product_tiepian_id and
s.product_level_id=@temp_product_level_id
--根据查询结果,没有新增,有,更新
if not exists
(
select
--@temp_stock_quantity=ss.stock_quantity
*
from
stock ss
where
ss.depart_id=@temp_depart_id and
ss.product_id=@temp_product_id and
ss.product_tiepian_id=@temp_product_tiepian_id and
ss.product_level_id=@temp_product_level_id
)
begin
insert stock
(depart_id,
product_id,
product_tiepian_id,
product_level_id,
in_stock_date,
stock_quantity
)
values
(
@temp_depart_id,
@temp_product_id,
@temp_product_tiepian_id,
@temp_product_level_id,
@temp_in_stock_date,
@temp_in_stock_quantity
)
end
else
begin
update stock
set
in_stock_date=@temp_in_stock_date,
stock_quantity=@temp_stock_quantity+@temp_in_stock_quantity
where
(
depart_id=@temp_product_id and
product_id=@temp_product_id and
product_tiepian_id=@temp_product_tiepian_id and
product_level_id=@temp_product_level_id
)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO