帮写一个触发器

[tvppf8590]

我有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的时候减

最终结果是两张表现在的值。
谢谢！

[Felixzhaowenzhong]

可以在 A 上面创建一个 instead of insert 触发器 来实现

[开着拖拉机泡妞]

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

[tvppf8590]

[Quote=引用 1 楼 的回复:]

可以在 A 上面创建一个 instead of insert 触发器 来实现
[/Quote]

创建触发器我知道，但是这种复杂的写不出来。。

[开着拖拉机泡妞]

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

--更正一下

[Felixzhaowenzhong]

[Quote=引用 2 楼 的回复:]
SQL code


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
……
[/Quote]

借用一下 2楼的

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

[tvppf8590]

[Quote=引用 4 楼 的回复:]

SQL code

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 ……
[/Quote]

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 行
')' 附近有语法错误。

[Felixzhaowenzhong]

[Quote=引用 6 楼 的回复:]
引用 4 楼 的回复:

SQL code

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 els……
[/Quote]

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

[tvppf8590]

[Quote=引用 7 楼 的回复:]

引用 6 楼 的回复:
引用 4 楼 的回复:

SQL code

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 ……
[/Quote]

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

[Felixzhaowenzhong]

[Quote=引用 8 楼 的回复:]
[Quote=引用 7 楼 的回复:]

引用 6 楼 的回复:
引用 4 楼 的回复:

SQL code

go
create trigger test on A
for insert
as
update B
set totalamount=m.amount

from（
select user,sum(amount) as amou……
[/Quote]

对
加上 “A”

[tvppf8590]

[Quote=引用 9 楼 的回复:]

[Quote=引用 8 楼 的回复:]
[Quote=引用 7 楼 的回复:]

引用 6 楼 的回复:
引用 4 楼 的回复:

SQL code

go
create trigger test on A
for insert
as
update B
set totalamount=m.amount

from（
select u……
[/Quote]

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' 无效。

[Felixzhaowenzhong]

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

--这个是我测试没有问题的

[tvppf8590]

[Quote=引用 11 楼 的回复:]

SQL code
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

cre……
[/Quote]

我把 instead of insert as 改成 for insert 就可以执行成功，不然就不成功，他们是什么区别呢