CREATE TRIGGER upd_return_triggerON [dbo].[STOCK_MALL_RETURNS] FOR UPDATE ASbeginupdate stock_mallset stock_mall.now_55=stock_mall.now_55 -(select sum(sm.return_55) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),
stock_mall.now_66=stock_mall.now_66 -(select sum(sm.return_66) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),
stock_mall.now_77=stock_mall.now_77 -(select sum(sm.return_77) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),
stock_mall.now_88=stock_mall.now_88 -(select sum(sm.return_88) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' )end
现在的问题是更新sm.status='1',sum(sm.return_55,66,77,88)是空值,而stock_mall.now_55,66,77,88却不能为空,请问各位这个trigger到底问题出现在哪里啊,小弟搞了两天也没解决好我用以下方法更新:CREATE TRIGGER upd_return_triggerON [dbo].[STOCK_MALL_RETURNS] FOR UPDATE ASbeginupdate stock_mallset stock_mall.now_55=stock_mall.now_55 -sm.return_55,
stock_mall.now_66=stock_mall.now_66 -sm.return_66,
stock_mall.now_77=stock_mall.now_77 -sm.return_77,
stock_mall.now_88=stock_mall.now_88 -sm.return_88
from inserted as smwhere stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1'
end也有点问题,可以更新数据,但两边更新的值不相等这是怎么回事
stock_mall.now_66=stock_mall.now_66 -(select sum(sm.return_66) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),
stock_mall.now_77=stock_mall.now_77 -(select sum(sm.return_77) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),
stock_mall.now_88=stock_mall.now_88 -(select sum(sm.return_88) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' )end
现在的问题是更新sm.status='1',sum(sm.return_55,66,77,88)是空值,而stock_mall.now_55,66,77,88却不能为空,请问各位这个trigger到底问题出现在哪里啊,小弟搞了两天也没解决好我用以下方法更新:CREATE TRIGGER upd_return_triggerON [dbo].[STOCK_MALL_RETURNS] FOR UPDATE ASbeginupdate stock_mallset stock_mall.now_55=stock_mall.now_55 -sm.return_55,
stock_mall.now_66=stock_mall.now_66 -sm.return_66,
stock_mall.now_77=stock_mall.now_77 -sm.return_77,
stock_mall.now_88=stock_mall.now_88 -sm.return_88
from inserted as smwhere stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1'
end也有点问题,可以更新数据,但两边更新的值不相等这是怎么回事
出现了不能更新将status='0'变成‘1’?
不能更新还是更新是不触发触发器
ON [dbo].[STOCK_MALL_RETURNS]
FOR UPDATE
AS
begin
update stock_mall
set
stock_mall.now_55=stock_mall.now_55 -isnull((select sum(sm.return_55) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_66=stock_mall.now_66 -isnull((select sum(sm.return_66) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_77=stock_mall.now_77 -isnull((select sum(sm.return_77) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_88=stock_mall.now_88 -isnull((select sum(sm.return_88) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0)
end
stock_mall.now_66=stock_mall.now_66 - sm.return_66,
stock_mall.now_77=stock_mall.now_77 - sm.return_77,
stock_mall.now_88=stock_mall.now_88 - sm.return_88
from
(Select
code,
color,
c_id,
status,
sum(return_55) As return_55,
sum(return_66) As return_66,
sum(return_77) As return_77,
sum(return_88) As return_88
From
inserted
Group By
code,
color,
c_id,
status)
as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' end
CREATE TRIGGER upd_return_triggerON [dbo].[STOCK_MALL_RETURNS] FOR UPDATE ASbeginupdate stock_mallset stock_mall.now_55=stock_mall.now_55 -
isnull((select sum(sm.return_55) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_66=stock_mall.now_66 -
isnull((select sum(sm.return_66) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_77=stock_mall.now_77 -
isnull((select sum(sm.return_77) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0),
stock_mall.now_88=stock_mall.now_88 -
isnull((select sum(sm.return_88) from inserted as sm where stock_mall.code=sm.code and stock_mall.color=sm.color and stock_mall.c_id= sm.c_id and sm.status = '1' ),0)end