22,207
社区成员
发帖
与我相关
我的任务
分享
我有2个表
表A
user isin amount
1 0 1
1 0 1
1 1 1
表B
user totalamount
1 1
需求是
当表A插入值的时候统计一次amount到表B的totalamount
条件 1.统计表A.user=B.user
2.当isin=0的时候加,ison=1的时候减
最终结果是两张表现在的值。
谢谢!
我把 instead of insert as 改成 for insert 就可以执行成功,不然就不成功,他们是什么区别呢
create table a ([user] int,isin int,amount int)
create table b ([user] int,totalamount int)
go
insert a select 1,0,1 union all select 1,0,1 union all select 1,1,1
insert b select 1,1
create trigger tr_name
on table_name
instead of insert
as
begin
update B
set totalamount=m.amount
from(
select [user],sum(amount) as amount
from(select [user],case when isin=0 then amount else -amount end as amount from A) t
group by [user])as m
where m.[user]=B.[user]
insert into a select * from inserted
end
select * from b
/*
user totalamount
1 1
*/
drop table a
drop table b
--这个是我测试没有问题的
create trigger Tr_IntegralJournalForTbuser
on dbo.Finance_IntegralJournal
instead of insert
as
begin
update dbo.User_TTBUser
set INTEGRAL=m.amount
from(
select TBNICK,sum(AMOUNT) as amount
from
(select TBNICK,
case when DIRECTION_FG=0 then AMOUNT else -AMOUNT end from dbo.Finance_IntegralJournal)
t
group by TBNICK) m
where m.TBNICK=dbo.User_TTBUser.NICK
end
消息 8155,级别 16,状态 2,过程 Tr_IntegralJournalForTbuser,第 6 行
没有为 't' 的列 2 指定任何列。
消息 207,级别 16,状态 1,过程 Tr_IntegralJournalForTbuser,第 9 行
列名 'AMOUNT' 无效。
create trigger Tr_IntegralJournalForTbuser
on dbo.Finance_IntegralJournal
instead of insert
as
begin
update dbo.User_TTBUser
set INTEGRAL=m.amount
from(
select TBNICK,sum(AMOUNT) as amount
from(select TBNICK,case when DIRECTION_FG=0 then AMOUNT else -AMOUNT end 这里是不是也少了表明A? ) t
group by TBNICK) m
where m.TBNICK=dbo.User_TTBUser.NICK
end
create trigger Tr_IntegralJournalForTbuser
on dbo.Finance_IntegralJournal
instead of insert
as
begin
update dbo.User_TTBUser
set INTEGRAL=m.amount
from(
select TBNICK,sum(AMOUNT) as amount
from(select TBNICK,case when DIRECTION_FG=0 then AMOUNT else -AMOUNT end) t
group by TBNICK) m
where m.TBNICK=dbo.User_TTBUser.NICK
end
你 少了一个 END
create trigger Tr_IntegralJournalForTbuser
on dbo.Finance_IntegralJournal
instead of insert
as
begin
update dbo.User_TTBUser
set INTEGRAL=m.amount
from(
select TBNICK,sum(AMOUNT) as amount
from(select TBNICK,case when DIRECTION_FG=0 then AMOUNT else -AMOUNT) t
group by TBNICK) m
where m.TBNICK=dbo.User_TTBUser.NICK
end
消息 102,级别 15,状态 1,过程 Tr_IntegralJournalForTbuser,第 10 行
')' 附近有语法错误。
create trigger tr_name
on table_name
instead of insert
as
begin
update B
set totalamount=m.amount
from(
select user,sum(amount) as amount
from(select user,case when isin=0 then amount else -amount)t
group by user)m
where m.user=B.user
insert into a select * from inserted
end
go
create trigger test on A
for insert
as
update B
set totalamount=m.amount
from(
select user,sum(amount) as amount
from(select user,case when isin=0 then amount else -amount end)t
group by user)mwherem.user=B.user
--更正一下
go
create trigger test on A
for insert
as
update B
set totalamount=m.amount
from(
select user,sum(amount) as amount
from(select user,case when isin=0 then amount else -amount)t
group by user)mwherem.user=B.user