"急救,在线等".....续

[beckhim]

可能我没有把业务逻辑说清楚，我想想决定把业务逻辑抛开，只提纯触发器的问题，然后我自己再试，因为我现对触发器还没有过多了解.
现在两个最简单的表：

表a
字段: id num
说明: 自动编号 数字

表r
字段： id before Amount after num
说明： 自动编号 insert前的数量 insert的数量 insert后的数量 数字
(所有字段类型都为int)

现在我想实现如下功能:
往表a中插入数字，一次插入若干个，但一次只会插入相同的数字，比如说一次插入4个15
然后在表r中记录每种数字在插入前/后和插入的数量
我自己写了一个触发器如下:
CREATE TRIGGER mytest ON a FOR INSERT AS
DECLARE @amount int, @num int, @after int,
@before int
SELECT @amount = COUNT(num)
FROM inserted
SELECT @num = num
FROM inserted
SELECT @before = COUNT(num)
FROM deleted
WHERE num = @num
SELECT @after = COUNT(num)
FROM inserted
WHERE num = @num
insert into r values(@before,@amount,@after,@num)

测试：
insert into a values(10)
insert into a values(10)
insert into a values(10)

表a的内容:
id num
1 10
2 10
3 10

表r的内容:
id before Amount after num
1 0 1 1 10
2 0 1 1 10
3 0 1 1 10


但我希望表r得到的结果应该是这样的:
id before Amount after num
1 0 3 3 10

请问我的触发器应该怎样写？

[zjcxc]

--或者是要这样的触发器

--示例表
create table a(id int identity,num int)
create table r(id int identity,before int,Amount int,[after] int,num int)
go

--实现处理的触发器
create trigger tr_a_insert on a
for insert
as
--统计
select num,cnt=count(*) into #i from inserted group by num

--更新已经存在的
update r set before=r.[after],Amount=i.cnt,[after]=r.[after]+i.cnt
from r,#i i where r.num=i.num

--插入不存在的
insert r(before,Amount,[after],num)
select 0,cnt,cnt,num from #i i
where not exists(select * from r where i.num=r.num)
go

--测试
insert into a values(10)
insert into a values(10)
insert into a values(10)

insert a select 11 union all select 11 union all select 11

insert a select 10 union all select 10

insert a select 11 union all select 10 union all select 12
go

--显示处理结果
select * from r
select * from a
go

--删除测试
drop table a,r

/*--测试结果

id before Amount after num
----------- ----------- ----------- ----------- -----------
1 5 1 6 10
2 3 1 4 11
3 0 1 1 12

（所影响的行数为 3 行）

id num
----------- -----------
1 10
2 10
3 10
4 11
5 11
6 11
7 10
8 10
9 11
10 10
11 12

（所影响的行数为 11 行）
--*/

[beckhim]

zjcxc(邹建)：谢谢，正在看

[yjdn]

楼主你不是要同一个num连接的要汇总，不连接的要分段记录吗？

[yjdn]

楼主只要一个num一条记录就行了？

[beckhim]

zjcxc(邹建)你好，我想再问一下，按照你写的第一个触发器，在表r中新记录的before是等于旧记录的after对吗？before可不可以在插入前从表a中统计出来？而after可不可以在插入后从表a中统计出来？谢谢

[beckhim]

不是，不是要一个num一条记录就行了，不管num是多少,要一个insert into一个记录,谢谢大家

[zjcxc]

那就是第二种触发器就行了

[yjdn]

汗，楼主，
你今天可苦了我了，让我白白想了那么多。

[beckhim]

zjcxc(邹建)老大：第二种触发器不是一个num一条记录?应该是第一种触发器吧，只要往表a中有一个insert操作，在表r中就要增加一条记录,只是第一种触发器中的一些东西我看不懂（水平太差），可否解释一下？还有:(before可不可以在插入前从表a中统计出来？而after可不可以在插入后从表a中统计出来？)。

yjdn(无尽天空) :今天谢谢你如此尽心尽力，我是看我的那些业务逻辑说的不清楚，大家都要绕着弯路走，所以我才想抛开业务逻辑，先学会写简单的触发器，然后自己再按照业务逻辑的需要重新写过。

[zjcxc]

开始是"一个num一条记录",后面又来个"一个insert into一个记录"
看来我是理不清楚了.

[zjcxc]

没有必要从表a做统计,这样只是浪费时间和没有效率.

因为每次insert都有记录的了,所以只要找到num对应的最后一次记录就可以得到before了
再统计就太浪费了.

[zjcxc]

--大致说明一下吧,希望你能看得懂

insert r(before,Amount,[after],num)
select before=isnull(r.[after],0)
,Amount=isnull(i.cnt,0)
,[after]=isnull(r.[after],0)+isnull(i.cnt,0)
,i.num
from( --统计本次插入各 num 的 Amount
select num,cnt=count(*) from inserted group by num
)i
left join( --用左连接,是因为有的 num 是第一次插入,这种情况在 r 表是没有记录的
--得到上次插入时,各 num 的 id
select id=max(id),num from r
--这个条件是限制只查询本次 insert 涉及到的 num ,没有必要把所有的 num 都找出来
where exists(select * from inserted i where i.num=r.num)
group by num
)r1 on i.num=r1.num
left join r on r1.id=r.id --这里是再与 r 表关联,得到各 num 最后一次的 after,做为本次的 before

[zjcxc]

因为做了通用性考虑:允许一次插入的记录中,可能有多个 num 的情况
所以触发器看起来复杂一些

[beckhim]

邹老大，先谢谢了，我是说:"不是，不是要一个num一条记录就行了"，可能是我说的不太清楚，俺天天一个在家里做事，很少与人说话，可能语言能力变弱了,能不能简单一些？一次插入的记录中,我的实际应用不可能有多个 num的情况。

[yjdn]

你表达最简单最直接的办法是：
把你所有可能出现的数据列出一部分，而不是说到一点列出一点。
然后把你操作后的产生的效果也列出来。你说得越多，就越让人乱，还不如用数据来说话。

[beckhim]

先假设表a和r都为空
操作1：
表a插入2个10
表a:
id num
1 10
2 10

表r:
id before Amount after num
1 0 2 2 10

操作2:
表a插入1个10
表a:
id num
1 10
2 10
3 10

表r:
id before Amount after num
1 0 2 2 10
2 2 1 3 10

操作3：
表a中将id为1的num改为11
表a:
id num
1 11
2 10
3 10

表r:
id before Amount after num
1 0 2 2 10
2 2 1 3 10
3 3 -1 2 10
4 0 1 1 11


备注：一次插入的记录中，不会有不同的num出现

[zjcxc]

真的糊涂了,我觉得我的两种触发器之一,应该是满足你的要求的.

关键是你要注意插入语句的处理
对于:
insert into a values(10)
insert into a values(10)
insert into a values(10)

SQL是认为做了三次插入,而不是你最先理解的,做了一次插入,这是无法改变的
要让上面的插入变为一次插入,只能改插入语句为:
insert into a select 10 union all select 10 union all select 10


所以我觉得问题应该是楼主在理解插入批次上出了问题,触发器本身应该是满足要求的了.

[zjcxc]

--按楼主写的进行测试

--测试表
create table a(id int identity,num int)
create table r(id int identity,before int,Amount int,[after] int,num int)
go

--实现处理的触发器
create trigger tr_a_insert on a
for insert,update,delete --如果不考虑删除的处理,则去掉 ,delete
as
--处理deleted表
insert r(before,Amount,[after],num)
select before=isnull(r.[after],0)
,Amount=-isnull(d.cnt,0)
,[after]=isnull(r.[after],0)-isnull(d.cnt,0)
,d.num
from(
select num,cnt=count(*) from deleted group by num
)d
left join(
select id=max(id),num from r
where exists(select * from deleted d where d.num=r.num)
group by num
)r1 on d.num=r1.num
left join r on r1.id=r.id

--处理inserted表
insert r(before,Amount,[after],num)
select before=isnull(r.[after],0)
,Amount=isnull(i.cnt,0)
,[after]=isnull(r.[after],0)+isnull(i.cnt,0)
,i.num
from(
select num,cnt=count(*) from inserted group by num
)i
left join(
select id=max(id),num from r
where exists(select * from inserted i where i.num=r.num)
group by num
)r1 on i.num=r1.num
left join r on r1.id=r.id
go

--操作1: 表a插入2个10
insert a select 10
union all select 10

--显示处理结果
select * from a
select * from r

/*--测试结果

id num
----------- -----------
1 10
2 10

（所影响的行数为 2 行）

id before Amount after num
----------- ----------- ----------- ----------- -----------
1 0 2 2 10

（所影响的行数为 1 行）
--*/
go

--操作2: 表a插入1个10
insert a select 10

--显示处理结果
select * from a
select * from r

/*--测试结果

id num
----------- -----------
1 10
2 10
3 10

（所影响的行数为 3 行）

id before Amount after num
----------- ----------- ----------- ----------- -----------
1 0 2 2 10
2 2 1 3 10

（所影响的行数为 2 行）
--*/
go

--操作3：表a中将id为1的num改为11
update a set num=11 where id=1

--显示处理结果
select * from a
select * from r

/*--测试结果

id num
----------- -----------
1 11
2 10
3 10

（所影响的行数为 3 行）

id before Amount after num
----------- ----------- ----------- ----------- -----------
1 0 2 2 10
2 2 1 3 10
3 3 -1 2 10
4 0 1 1 11

（所影响的行数为 4 行）
--*/
go

--操作4：删除表a中id=3的记录
delete from a where id=3

--显示处理结果
select * from a
select * from r

/*--测试结果

id num
----------- -----------
1 11
2 10

（所影响的行数为 2 行）

id before Amount after num
----------- ----------- ----------- ----------- -----------
1 0 2 2 10
2 2 1 3 10
3 3 -1 2 10
4 0 1 1 11
5 2 -1 1 10

（所影响的行数为 5 行）
--*/
go

--删除测试
drop table a,r

[beckhim]

好的，谢谢，我试一试

[beckhim]

邹老大，yjdn(无尽天空),先谢谢了，我先按照邹老大给出的触发器实现一下我的实际应用，有什么问题再来请教