相关触发器:CREATE TRIGGER purproductdetail_insert ON dbo.purproductdetail
FOR INSERT
AS
DECLARE @State bit,@oldState
declare @itemon nvarchar(50)
SET NOCOUNT ON
select @state=state,@itemon=itemon from inserted
select @oldstate=state from deleted if update(state)
begin
if @state=2 and @oldstate=1
update tablec set Amount=Amount+i.Amount from tablec,inserted i where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon
endSET NOCOUNT OFF
相关的更新语句:update purproductdetail set state=2 where itemon in('01','02','03','A01','03')
/*
结果TABLEC表的记录有的被触发器更新了amount字段,有的没有,原因到底是??
*/
FOR INSERT
AS
DECLARE @State bit,@oldState
declare @itemon nvarchar(50)
SET NOCOUNT ON
select @state=state,@itemon=itemon from inserted
select @oldstate=state from deleted if update(state)
begin
if @state=2 and @oldstate=1
update tablec set Amount=Amount+i.Amount from tablec,inserted i where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon
endSET NOCOUNT OFF
相关的更新语句:update purproductdetail set state=2 where itemon in('01','02','03','A01','03')
/*
结果TABLEC表的记录有的被触发器更新了amount字段,有的没有,原因到底是??
*/
FOR UPDATE
AS
DECLARE @State bit,@oldState
declare @itemon nvarchar(50)
SET NOCOUNT ON
select @state=state,@itemon=itemon from inserted
select @oldstate=state from deleted if update(state)
begin
if @state=2 and @oldstate=1
update tablec set Amount=Amount+i.Amount from tablec,inserted i where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon
endSET NOCOUNT OFF
select @oldstate=state from deleted
你这样变量赋值只是最后一个记录的数值,而不是批量数值。如果要批量数值的话,必须要把INSERTED和DELETED表关联起来。
不存在批量更新导致失败的。
if update(state)
begin
if @state=2 and @oldstate=1
update tablec set Amount=Amount+i.Amount from tablec,inserted i, deleted d where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon and i.key=d.key
end
begin
update tablec set Amount=Amount+i.Amount from tablec,inserted i, deleted d where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon and i.key=d.key and i.state=2 and d.state=1
end
CREATE TRIGGER purproductdetail_insert ON dbo.purproductdetail
FOR INSERT --应该是for update吧
AS
SET NOCOUNT ON
update tablec set Amount=Amount+i.Amount
from tablec,inserted i,deleted j
where tablec.itemon=i.itemon and tablec.Storeon=i.Storeon and (此处输入表i,与表j 连接的条件)
and i.state=2 and j.state=1
SET NOCOUNT OFF