CREATE TRIGGER UPDate_User_Card ON [dbo].[detailconsumption]
FOR INSERT, UPDATE
AS
BEGINDECLARE @aa TABLE(userid int,yue decimal(9,1))insert into @aa(userid,yue)
select A.userid,
sum(case status when 1 then price
else -(price) end)
from inserted A INNER JOIN detailconsumption B
ON B.userid = A.userid
group A.useridupdate B
set B.cardmoney = A.yue
from @aa A inner join usersystem B
on B.userid =A.useridupdate B
set B.Price = A.yue
from @aa A inner join CardUser B
on B.userid =A.useridEND
FOR INSERT, UPDATE
AS
BEGINDECLARE @aa TABLE(userid int,yue decimal(9,1))insert into @aa(userid,yue)
select A.userid,
sum(case status when 1 then price
else -(price) end)
from inserted A INNER JOIN detailconsumption B
ON B.userid = A.userid
group A.useridupdate B
set B.cardmoney = A.yue
from @aa A inner join usersystem B
on B.userid =A.useridupdate B
set B.Price = A.yue
from @aa A inner join CardUser B
on B.userid =A.useridEND
解决方案 »
- 如何用SQL在有序的表中查找出遗漏的数据,例如某字段 1,3,4,5,6,8....如何查找 2,7不存在
- 函数里面能实现动态读取表里面的任何列的数据么?
- 如何在一句SQL语句中返回含Count()语句的列?
- 数据库有mdf文件和ldf文件,如何还原本数据库
- 大侠们,为什么sql2008升级到sql2012后慢了几百倍
- 请问在创建表时的列名为什么变量用不了??
- SOS!数据库导入问题!100分在线等待马上给分!
- ado的问题?iis的问题?还是系统的问题?
- 一个select 语句的问题
- 能不能从一个数据库中的数据表每月抽取当月输入的数据到另外一个数据库的数据表。
- 请问各位大侠,如何实现异地差异备份(100分)
- 关于SQL语句统计的问题
insert detailconsumption values(...)
insert detailconsumption values(...)
还是
insert detailconsumption
select * from ......
FOR INSERT, UPDATE
AS
BEGINDECLARE @aa TABLE(userid int,yue decimal(9,1))insert into @aa(userid,yue)
select A.userid,
sum(case B.status when 1 then B.price
when 0 then -(B.price) end)
from inserted A INNER JOIN detailconsumption B
ON B.userid = A.userid
group A.useridupdate B
set B.cardmoney = A.yue
from @aa A inner join usersystem B
on B.userid =A.useridupdate B
set B.Price = A.yue
from @aa A inner join CardUser B
on B.userid =A.useridEND
FOR INSERT, UPDATE
ASupdate usersystem set cardmoney=isnull(tem.yuer,0) from
(select userid,sum(case when status=1 then -price else price end) yuer from detailconsumption where userid in (select userid from inserted)) tem
where usersystem.username=tem.useridupdate CardUser set cardmoney=isnull(tem.yuer,0) from
(select userid,sum(case when status=1 then -price else price end) yuer from detailconsumption where userid in (select userid from inserted)) tem
where usersystem.Userid=tem.userid
FOR INSERT, UPDATE
AS
update usersystem set cardmoney=isnull(tem.yuer,0) from (select userid,sum(case when status=1 then -price else price end) yuer from detailconsumption where userid in (select userid from inserted) group by userid) tem where usersystem.username=tem.useridupdate CardUser set cardmoney=isnull(tem.yuer,0) from (select userid,sum(case when status=1 then -price else price end) yuer from detailconsumption where userid in (select userid from inserted) group by userid) tem where usersystem.Userid=tem.userid