请指正下列触发器中的错误
CREATE TRIGGER Ck_BcpIn_Dtl_ino ON dbo.Ck_BcpIn_Dtl
FOR insert
AS
Begin
Set NoCount On
declare @Container_Code as Nvarchar(10)
declare @Ymonth as Nvarchar(6)
declare @type as Nvarchar(10)
declare @store as Nvarchar(10)
declare @cp_model As NvarChar(18)
declare @cp_name As NvarChar(30)
declare @cp_size As NvarChar(10)
declare @cp_packing As NvarChar(10)
declare @cp_grade As NvarChar(10)
declare @cp_sb As NvarChar(10)
declare @cp_stplace As NvarChar(10)
declare @cp_pc As NvarChar(10)
declare @In_Num As Numeric(9,0)
DECLARE Temp_Table CURSOR FOR
Select Container_Code,Ymonth,type,Store,Cp_model,cp_name,Cp_size,cp_packing,
cp_grade,cp_sb,cp_stplace,cp_pc,in_num from inserted
OPEN Temp_Table
FETCH NEXT FROM Temp_Table
INTO @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @In_Num
WHILE @@FETCH_STATUS = 0
BEGIN
update Bcp_Xs_StDepInfoOut set Sdinfo_In= Sdinfo_In+(@In_Num*@cp_packing) Where
Container_Code=@Container_Code and Ymonth=@Ymonth And type=@type and Store=@Store
and cp_model=@cp_model and cp_name=@cp_name and cp_size=@cp_size and cp_packing=@cp_packing
and cp_grade=@cp_grade and cp_sb=@cp_sb and cp_stplace=@cp_stplace and cp_pc=@cp_pc
if @@rowcount=0
Begin
Insert into Bcp_Xs_StDepInfoOut(container_code,ymonth,type, store, cp_model, cp_name,cp_size,cp_packing,cp_grade,cp_sb,cp_stplace,cp_pc,sdinfo_in)
Values ( @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @in_Num)
End
FETCH NEXT FROM Temp_Table
INTO @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @in_Num
END
CLOSE Temp_Table
DEALLOCATE Temp_Table
Set NoCount Off
[解决办法]
CREATE TRIGGER Ck_BcpIn_Dtl_ino ON dbo.Ck_BcpIn_Dtl
FOR insert
AS
Begin
--Set NoCount On
declare @Container_Code as Nvarchar(10)
declare @Ymonth as Nvarchar(6)
declare @type as Nvarchar(10)
declare @store as Nvarchar(10)
declare @cp_model As NvarChar(18)
declare @cp_name As NvarChar(30)
declare @cp_size As NvarChar(10)
declare @cp_packing As NvarChar(10)
declare @cp_grade As NvarChar(10)
declare @cp_sb As NvarChar(10)
declare @cp_stplace As NvarChar(10)
declare @cp_pc As NvarChar(10)
declare @In_Num As Numeric(9,0)
DECLARE Temp_Table CURSOR FOR
Select Container_Code,Ymonth,type,Store,Cp_model,cp_name,Cp_size,cp_packing,
cp_grade,cp_sb,cp_stplace,cp_pc,in_num from inserted
OPEN Temp_Table
FETCH NEXT FROM Temp_Table
INTO @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @In_Num
WHILE @@FETCH_STATUS = 0
BEGIN
update Bcp_Xs_StDepInfoOut set Sdinfo_In= Sdinfo_In+(@In_Num*@cp_packing) Where
Container_Code=@Container_Code and Ymonth=@Ymonth And type=@type and Store=@Store
and cp_model=@cp_model and cp_name=@cp_name and cp_size=@cp_size and cp_packing=@cp_packing
and cp_grade=@cp_grade and cp_sb=@cp_sb and cp_stplace=@cp_stplace and cp_pc=@cp_pc
if @@rowcount=0
Begin
Insert into Bcp_Xs_StDepInfoOut(container_code,ymonth,type, store, cp_model, cp_name,cp_size,cp_packing,cp_grade,cp_sb,cp_stplace,cp_pc,sdinfo_in)
Values ( @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @in_Num)
End
FETCH NEXT FROM Temp_Table
INTO @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @in_Num
END
CLOSE Temp_Table
DEALLOCATE Temp_Table
--Set NoCount Off
[解决办法]
似乎无错
update 与insert中的sdinfo_in列不统一
update 为@In_Num*@cp_packing
insert 为@in_num
[解决办法]
这么多字段如果一个是null将有问题
update 语句中cp_packing出现在set和where中，虽然没有语法问题，但比较怪异
如cxmcxm(小陈) 所说，insert 语句应该改为
Insert into Bcp_Xs_StDepInfoOut(container_code,ymonth,type, store, cp_model, cp_name,cp_size,cp_packing,cp_grade,cp_sb,cp_stplace,cp_pc,sdinfo_in)
Values ( @Container_Code,@Ymonth, @type,@Store,@cp_model,@cp_name,@cp_size,@cp_packing,@cp_grade,@cp_sb,@cp_stplace,@cp_pc, @in_Num*@cp_packing)
不用游标（未测试）
CREATE TRIGGER Ck_BcpIn_Dtl_ino ON dbo.Ck_BcpIn_Dtl
FOR insert
AS
Begin
Set NoCount On
update b
set Sdinfo_In= b.Sdinfo_In+t.num
from Bcp_Xs_StDepInfoOut b,(
select sum(isnull(Sdinfo_In,0)*isnull(cp_packing,0)) as num,
Container_Code,Ymonth,type,Store,cp_model,cp_name,cp_size,
cp_packing,
cp_grade,cp_sb,cp_stplace,cp_pc
from inserted
group by
Container_Code,Ymonth,type,Store,cp_model,cp_name,cp_size,
cp_packing,
cp_grade,cp_sb,cp_stplace,cp_pc
) as t
Where
b.Container_Code=t.Container_Code and
b.Ymonth=t.Ymonth And
b.type=t.type and
b.Store=t.Store and
b.cp_model=t.cp_model and
b.cp_name=t.cp_name and
b.cp_size=t.cp_size and
b.cp_packing=t.cp_packing and
b.cp_grade=t.cp_grade and
b.cp_sb=t.cp_sb and
b.cp_stplace=t.cp_stplace and
b.cp_pc=t.cp_pc
Insert into Bcp_Xs_StDepInfoOut(container_code,ymonth,type, store, cp_model, cp_name,cp_size,cp_packing,cp_grade,cp_sb,cp_stplace,cp_pc,sdinfo_in)
select container_code,ymonth,type, store, cp_model,
cp_name,cp_size,cp_packing,cp_grade,cp_sb,cp_stplace,cp_pc,
sum(isnull(Sdinfo_In,0)*isnull(cp_packing,0))
from inserted i
where not exists (
select 1 from Bcp_Xs_StDepInfoOut b
where
b.Container_Code=t.Container_Code and
b.Ymonth=t.Ymonth And
b.type=t.type and
b.Store=t.Store and
b.cp_model=t.cp_model and
b.cp_name=t.cp_name and
b.cp_size=t.cp_size and
b.cp_packing=t.cp_packing and
b.cp_grade=t.cp_grade and
b.cp_sb=t.cp_sb and
b.cp_stplace=t.cp_stplace and
b.cp_pc=t.cp_pc
)
group by
Container_Code,Ymonth,type,Store,cp_model,cp_name,cp_size,
cp_packing,
cp_grade,cp_sb,cp_stplace,cp_pc
Set NoCount Off
