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
ENDCLOSE Temp_Table
DEALLOCATE Temp_Table
Set NoCount Off
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
ENDCLOSE Temp_Table
DEALLOCATE Temp_Table
Set NoCount Off
解决方案 »
- 如何查询字符类型字段中带有小数点的数值的记录
- wampserver2.0i 下 show variables 找不到datadir变量
- 字符串排序问题
- 关于数据库字符串的两个函数用法
- 求存储过程参数的用法
- 急求解,订阅发布导致 pages/sec值高
- a.mdf 700M,a_log.ldf 1.7G.怎么把a_log.ldf变成10M?
- 请问一下,如何用alter table...add constrarnt...primary key,来加主键约束
- 请教,帮忙!关于"PRIMAR"已满
- 一个改过自新的软工大二学生求助
- 有比企业管理器更好的客户端工具吗
- 请高手看看,求一个SQL语句,稍微有些难吧!
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
ENDCLOSE Temp_Table
DEALLOCATE Temp_Table
--Set NoCount Off
update 与insert中的sdinfo_in列不统一
update 为@In_Num*@cp_packing
insert 为@in_num
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_pcInsert 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_pcSet NoCount Off