首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

mssql trigger,该怎么处理

2011-12-31 
mssql triggerCREATETRIGGERupd_return_triggerON[dbo].[STOCK_MALL_RETURNS]FORUPDATEASbeginupdatestock

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

热点排行