mssql trigger
CREATE TRIGGER upd_return_trigger
ON [dbo].[STOCK_MALL_RETURNS]
FOR UPDATE
AS
begin
update stock_mall
set
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_trigger
ON [dbo].[STOCK_MALL_RETURNS]
FOR UPDATE
AS
begin
update stock_mall
set
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 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_trigger
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
[解决办法]
--try
CREATE TRIGGER upd_return_trigger
ON [dbo].[STOCK_MALL_RETURNS]
FOR UPDATE
AS
begin
update stock_mall
set
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
(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
[解决办法]
用isnull使null为0
CREATE TRIGGER upd_return_trigger
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