触发器高手,帮我看看,多条记录更新问题!
CREATE TRIGGER tu_KC_OUT_DETAIL ON dbo.KC_OUT_DETAIL
FOR UPDATE
AS
begin
--begin transaction
declare
@numrows int,
@saveflag int,
@errno int,
@nrow int,
@errmsg varchar(255),
@S_ID_NO varchar(10),
@S_入库ID varchar(10),
@s_标志 char(1),
@s_存盘标志 varchar(2),
@s_客户编码N varchar(5),
@S_名称 varchar(20),
@s_批号 varchar(30),
---------------------------------------------
@S_ID_NO1 varchar(10),
@S_入库ID1 varchar(10),
@s_客户编码N1 varchar(5),
@S_名称1 varchar(20),
@s_批号1 varchar(30),
----------------------------
@n_数量N numeric(18,2),
@n_金额N numeric(18,2),
---------------------
@s_调拔单号D varchar(7),
@s_客户编码D varchar(5),
@s_品种D varchar(10),
@s_产品批号D varchar(30),
@n_数量D numeric(18,2),
@n_金额D numeric(18,2),
@n_余额数量 numeric(18,2),
@n_余额金额 numeric(18,2),
@s_流水号 varchar(11),
@s_序号 varchar(3)
select @numrows = @@rowcount
if @numrows = 0 return
--声明删除游标
Declare Deleted_Cursor Cursor For
Select rtrim(名称), 批号, ID_NO, 入库ID, 标志, 存盘标志 From Deleted
For Read Only
--声明插入游标
Declare Inserted_Cursor Cursor For
Select rtrim(名称), 批号, ID_NO, 入库ID, 标志 From Inserted
For Read Only
--打开游标
Open Deleted_Cursor
--获得首行记录
Fetch Next From Deleted_Cursor into @S_名称, @s_批号, @S_ID_NO, @S_入库ID, @s_标志,@s_存盘标志
--循环游标
while @@Fetch_Status = 0
Begin
if isnull(@s_存盘标志,'') = 'Y'
--已经保存了
begin
if isnull(@n_数量N,0) <> 0 and isnull(@S_名称,'') <> '' and isnull(@S_批号,'') <> '' and isnull(@S_入库ID,'') <> ''
begin
update KC_IN_DETAIL set 已出库数量 = isnull(已出库数量,0) - isnull(@n_数量N,0), 余额数量 = isnull(数量,0) - isnull(已出库数量,0) + isnull(@n_数量N,0) where ID_NO =@S_入库ID and 批号 = @s_批号 and 名称 =@S_名称
end
end
Open Inserted_Cursor
Fetch Next From Inserted_Cursor into @S_名称1, @s_批号1, @S_ID_NO, @S_入库ID1, @s_标志
while @@Fetch_Status = 0
if isnull(@n_数量N,0) = 0
begin
Close Deleted_Cursor--关闭游标
Close Inserted_Cursor--关闭游标
Deallocate Deleted_Cursor--删除游标
Deallocate Inserted_Cursor--删除游标
return
end
if isnull(@S_名称,'') = '' or isnull(@S_批号,'') = '' or isnull(@S_入库ID,'') = ''
begin
select @errno = 13000
select @errmsg = '名称、批号不能为空......'
goto error
return
end
if isnull(@S_名称1,'') = @S_名称 and isnull(@S_批号1,'') =@S_批号 and isnull(@S_入库ID1,'') =@S_入库ID
begin
if isnull(@s_标志,'') = 'Y'
begin
update KC_IN_DETAIL set 已出库数量 = isnull(已出库数量,0) + isnull(@n_数量N,0), 余额数量 = isnull(数量,0) - isnull(已出库数量,0) - isnull(@n_数量N,0)
where ID_NO =@S_入库ID and 批号 = @s_批号 and 名称 =@S_名称
select @n_余额数量 = 余额数量 from KC_IN_DETAIL where ID_NO =@S_入库ID and 名称 =@S_名称 and 批号 = @s_批号
if isnull(@n_余额数量,0) < 0
begin
select @errno = 13000
select @errmsg = '入库单<No:'+@S_ID_NO+'>数量不足,请调整出库数量。您以要在原来的基础上减'+convert(varchar(10),@n_余额数量*(-1))+ ',调整为 '+convert(varchar(10),@n_数量N + @n_余额数量)
goto error
end
end
end
Fetch Next From Inserted_Cursor into @S_名称1, @s_批号1, @S_ID_NO, @S_入库ID1, @s_标志
END
Close Inserted_Cursor
--获取下一行
Fetch Next From Deleted_Cursor into @S_名称, @s_批号, @S_ID_NO, @S_入库ID, @s_标志,@s_存盘标志
End
Close Deleted_Cursor--关闭游标
Deallocate Deleted_Cursor--删除游标
Deallocate Inserted_Cursor--删除游标
return
error:
raiserror @errno @errmsg
rollback transaction
Close Deleted_Cursor--关闭游标
Deallocate Deleted_Cursor--删除游标
Deallocate Inserted_Cursor--删除游标