sos求救关于后台数据库触发器的问题 ( 积分: 100 )

CREATE TRIGGER lktr ON [dbo].[lk]
FOR INSERT, UPDATE, DELETE
AS
declare @dec1 decimal,@dec2 decimal ,@dec3 decimal,@dec4 decimal,
@str1 char(15),@str2 char(15),@str3 char(15),
@int1 integer,@int2 integer
select @int1=fid,@str1=ckcode from inserted
select @int2=fid,@str2=ckcode from deleted
if (@str1 is not null) and (@str2 is not null)
begin
update storage set qnty=a.qnty+b.qnty,amount=a.amount+b.qnty*b.price from storage a,lkpart b where ckcode=@str1 and a.m_code=b.m_code and b.lkfid=@int1
update storage set qnty=a.qnty-b.qnty,amount=a.amount-b.qnty*b.price from storage a,lkpart b where ckcode=@str2 and a.m_code=b.m_code and b.lkfid=@int1
insert storage(m_code,ckcode,qnty,amount) select m_code,@str1,qnty,qnty*price from lkpart where lkfid=@int1 and m_code not in (select m_code from storage where ckcode=@str1)
end
说明:
这个一个仓库进库管理时的一个仓库入库表的触发器,表名为lk,包含有一个字段是ckcode的意思是仓库编码
lkpart表是入库明细表
storage是库存表
做该触发器的主要目的是为了避免当入错仓库时改仓库时让该触发器自动更改库存.
但在调试时会出现下列错误:
1.键列信息不足或不正确,更新影响到过多的行(当库存表中有数据,即update....语句执行)
2.无法为更新行集定位:一些值可能在最后读取后发变(当库存表中没有数据,即insert ...语句执行)
sql跟踪如下:
sql:stmtcompleted UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3
CREATE TRIGGER lktr ON dbo.lk
FOR INSERT, UPDATE, DELETE
AS
select @int1=fid,@str1=ckcode from inserted
select @int2=fid,@str2=ckcode from deleted

if @str1 is not null and @str2 is not null
update storage set qnty=a.qnty+b.qnty,amount=a.amount+b.qnty*b.price from storage a,lkpart b where ckcode=@str2 and a.m_code=b.m_code and b.lkfid in (select fid from inserted)
update storage set qnty=a.qnty-b.qnty,amount=a.amount-b.qnty*b.price from storage a,lkpart b where ckcode=@str1 and a.m_code=b.m_code and b.lkfid in (select fid from deleted)
insert storage(m_code,ckcode,qnty,amount) select m_code,@str1,qnty,qnty*price from lkpart where lkfid=@int1 and m_code not in (select m_code from storage where ckcode=@str1)
sql:stmtcompleted:UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3
exec sp_executesql N'UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3', N'@P1 varchar(10),@P2 varchar(20),@P3 varchar(10)', '004 ', 'LK20050915002 ', '001 '

CREATE TRIGGER lktr ON [dbo].[lk]
FOR INSERT, UPDATE, DELETE
AS
declare @dec1 decimal,@dec2 decimal ,@dec3 decimal,@dec4 decimal,
@str1 char(15),@str2 char(15),@str3 char(15),
@int1 integer,@int2 integer
select @int1=fid,@str1=ckcode from inserted
select @int2=fid,@str2=ckcode from deleted
if (@str1 is not null) and (@str2 is not null)
begin
update storage set qnty=a.qnty+b.qnty,amount=a.amount+b.qnty*b.price from storage a,lkpart b where ckcode=@str1 and a.m_code=b.m_code and b.lkfid=@int1
update storage set qnty=a.qnty-b.qnty,amount=a.amount-b.qnty*b.price from storage a,lkpart b where ckcode=@str2 and a.m_code=b.m_code and b.lkfid=@int1
insert storage(m_code,ckcode,qnty,amount) select m_code,@str1,qnty,qnty*price from lkpart where lkfid=@int1 and m_code not in (select m_code from storage where ckcode=@str1)
end
说明:
这个一个仓库进库管理时的一个仓库入库表的触发器,表名为lk,包含有一个字段是ckcode的意思是仓库编码
lkpart表是入库明细表
storage是库存表
做该触发器的主要目的是为了避免当入错仓库时改仓库时让该触发器自动更改库存.
但在调试时会出现下列错误:
1.键列信息不足或不正确,更新影响到过多的行(当库存表中有数据,即update....语句执行)
2.无法为更新行集定位:一些值可能在最后读取后发变(当库存表中没有数据,即insert ...语句执行)
sql跟踪如下:
sql:stmtcompleted UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3
CREATE TRIGGER lktr ON dbo.lk
FOR INSERT, UPDATE, DELETE
AS
select @int1=fid,@str1=ckcode from inserted
select @int2=fid,@str2=ckcode from deleted

if @str1 is not null and @str2 is not null
update storage set qnty=a.qnty+b.qnty,amount=a.amount+b.qnty*b.price from storage a,lkpart b where ckcode=@str2 and a.m_code=b.m_code and b.lkfid in (select fid from inserted)
update storage set qnty=a.qnty-b.qnty,amount=a.amount-b.qnty*b.price from storage a,lkpart b where ckcode=@str1 and a.m_code=b.m_code and b.lkfid in (select fid from deleted)
insert storage(m_code,ckcode,qnty,amount) select m_code,@str1,qnty,qnty*price from lkpart where lkfid=@int1 and m_code not in (select m_code from storage where ckcode=@str1)
sql:stmtcompleted:UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3
exec sp_executesql N'UPDATE "fx".."lk" SET "ckcode"=@P1 WHERE "pageno"=@P2 AND "ckcode"=@P3', N'@P1 varchar(10),@P2 varchar(20),@P3 varchar(10)', '004 ', 'LK20050915002 ', '001 '

建议加上begin trans 和commit trans