• 主页
• 基础类
• 应用实例
• 新技术前沿

# 紧急（触发器问题)

yangq4000 2003-12-11 05:18:00

bh a b
1001 1 1
1002 2 2
1001 2 2
1002 3 3

bh m [5%] [10%]
1001 a 0.15 0.3
1001 b 0.15 0.3
1002 a 0.25 0.5
1002 b 0.25 0.5

1001 m [5%]
0.15=sum(t1.a) where bh=1001

...全文
4 点赞 收藏 4

4 条回复

victorycyz 2003-12-12

DigJim 2003-12-11

CREATE TRIGGER test ON t1
FOR update
AS
declare @sumA decimal(18,2)
declare @sumB decimal(18,2)

select @sumA=sum(t1.a),@sumB=sum(t1.b) from t1
where t1.bh=(select bh from inserted)

update t2 set [5%] =(sumA*0.05),[10%]=(sumA*0.25)
where t2.bh=(select bh from inserted) AND m='a'

update t2 set [5%] =(sumB*0.05),[10%]=(sumB*0.25)
where t2.bh=(select bh from inserted) AND m='b'
go

DigJim 2003-12-11
CREATE TRIGGER test ON t1
FOR update
AS
declare @sumA decimal(18,2)
declare @sumB decimal(18,2)

select @sumA=sum(t1.a),@sumB=sum(t1.b) from t1
where t1.bh=(select bh from inserted)

update t2 set [5%] =(sumA*0.05),[10%]=(sumB*0.25)
where t2.bh=(select bh from inserted)
go

shuiniu 2003-12-11

create table t1(bh char(4), a float,b float)
insert t1 values('1001',1,1)
insert t1 values('1002',2,2)
insert t1 values('1001',2,2)
insert t1 values('1002',3,3)

create view test
as
select bh,'a' m,sum(a) * 0.05 [5%],sum(b)*0.1 as [10%]
from t1
group by bh
union all
select bh,'b', sum(a) * 0.05 [5%],sum(b)*0.1 [10%]
from t1
group by bh
go

select * from test order by bh

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区