触发器综述之四
统计类触发器
统计类触发器的功能是根据业务表的数据变化实时修改统计表的数据，这类触发器因为需要统计，所以往往非常复杂，同时这类触发器往往应用于增删改频繁的主要业务表，对性能的影响比较大，所以这类触发器的写法必须非常经济。
从功能上来说，一般此类触发器所在的表往往是一个经常被客户端程序操作的表，增删改频繁，而且很可能多个表的数据变化都需要更新同一个统计表，性能的要求比较高。另一方面，往往有实时性要求，就是在业务表修改同时修改统计表，保证统计表的数据反映的是最后的统计信息。
从技术上，这类触发器的代码复杂程度往往是由统计的内容决定的，这里要求对需要处理的逻辑关系非常清楚，同时由于对性能的要求比较高，触发器代码必须高效，很可能需要配合必要的索引保证触发起的性能，临时表和表变量的使用需要特别谨慎。
这类触发器的统计表往往是个冗余表，因为利用业务表数据，完全可以统计出统计表的所有记录，而且统计表数据一般不允许客户端进行直接修改。关于使用一个统计存储过程来计算统计信息的做法和用触发器进行统计的方法比较，个人认为：
1、如果不用触发器，统计存储过程需要取出所有业务表的进行计算，这样如果实时性要求高，调用很多，性能上的消耗相当大。
2、触发器只是在原有统计的基础上加上本次修改对统计表的影响，所以计算量小，基本不会重复计算，在这个方面，对提高性能是有帮助的。
3、存储过程调用的时候就必须做完全的统计，会造成调用的时间段内的服务器负担急剧增加，而触发器把统计工作分摊到业务表修改的每个时间点，分摊了服务器负担。
4、对于实时性要求不高的应用，因为统计可以用存储过程实现后用JOB调度在比较空闲时间运行，从性能考虑应该不使用触发器。
下面用一个简化了的库存统计例子来说明这类触发器。需求如下：
一个进货表和一个出货表，都有物品ID、仓库ID、数量、单价字段，库存表有物品ID、仓库ID、数量、金额字段，要求库存表反映即时库存，表结构和触发器以及测试代码如下（无关字段已经忽略）：
CREATE TABLE STORE (
PRDID INT NOT NULL, --物品ID
STOID INT NOT NULL, --仓库ID
QTY INT NOT NULL DEFAULT (0), --数量
MON NUMERIC(10,2) DEFAULT(0) --金额
)
GO
CREATE TABLE PRDIN (
PRDID INT NOT NULL, --物品ID
STOID INT NOT NULL, --仓库ID
QTY INT NOT NULL DEFAULT (0), --数量
PRICE NUMERIC(10,2) DEFAULT(0)--单价
)
GO
CREATE TABLE PRDOUT (
PRDID INT NOT NULL, --物品ID
STOID INT NOT NULL, --仓库ID
QTY INT NOT NULL DEFAULT (0), --数量
PRICE NUMERIC(10,2) DEFAULT(0)--单价
)
GO
CREATE TRIGGER TR_PRDIN
ON PRDIN
FOR INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON
--已有的更新数量和金额
UPDATE S SET
QTY =S.QTY +T.QTY ,
MON =S.MON +T.MON
FROM STORE S,
(
SELECT
PRDID ,
STOID ,
QTY =SUM(QTY),
MON =SUM(MON)
FROM (
SELECT
PRDID ,
STOID ,
QTY ,
MON =QTY*PRICE
FROM INSERTED
UNION ALL
SELECT
PRDID ,
STOID ,
QTY =-QTY,
MON =-QTY*PRICE
FROM DELETED
) AS T1
GROUP BY
PRDID ,
STOID
) AS T
WHERE S.PRDID =T.PRDID AND
S.STOID =T.STOID
--没有的插入数据
INSERT STORE (
PRDID ,
STOID ,
QTY ,
MON )
SELECT
PRDID ,
STOID ,
QTY =SUM(QTY),
MON =SUM(MON)
FROM (
SELECT
PRDID ,
STOID ,
QTY ,
MON =QTY*PRICE
FROM INSERTED
UNION ALL
SELECT
PRDID ,
STOID ,
QTY =-QTY,
MON =-QTY*PRICE
FROM DELETED
) AS T
WHERE NOT EXISTS (
SELECT
1
FROM STORE S
WHERE S.PRDID =T.PRDID AND
S.STOID =T.STOID
)
GROUP BY
PRDID ,
STOID
GO
CREATE TRIGGER TR_PRDOUT
ON PRDOUT
FOR INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON
--已有的更新数量和金额
UPDATE S SET
QTY =S.QTY +T.QTY ,
MON =S.MON +T.MON
FROM STORE S,
(
SELECT
PRDID ,
STOID ,
QTY =SUM(QTY),
MON =SUM(MON)
FROM (
SELECT
PRDID ,
STOID ,
QTY =-QTY,
MON =-QTY*PRICE
FROM INSERTED
UNION ALL
SELECT
PRDID ,
STOID ,
QTY =QTY,
MON =QTY*PRICE
FROM DELETED
) AS T1
GROUP BY
PRDID ,
STOID
) AS T
WHERE S.PRDID =T.PRDID AND
S.STOID =T.STOID
--没有的插入数据
INSERT STORE (
PRDID ,
STOID ,
QTY ,
MON )
SELECT
PRDID ,
STOID ,
QTY =SUM(QTY),
MON =SUM(MON)
FROM (
SELECT
PRDID ,
STOID ,
QTY =-QTY,
MON =-QTY*PRICE
FROM INSERTED
UNION ALL
SELECT
PRDID ,
STOID ,
QTY =QTY,
MON =QTY*PRICE
FROM DELETED
) AS T
WHERE NOT EXISTS (
SELECT
1
FROM STORE S
WHERE S.PRDID =T.PRDID AND
S.STOID =T.STOID
)
GROUP BY
PRDID ,
STOID
GO
--测试插入一行PRDIN
INSERT PRDIN
VALUES (1,100,20,11.6)
SELECT * FROM STORE
GO
--结果
--测试插入多行PRDIN
INSERT PRDIN
SELECT 1,100,30,12.2
UNION ALL
SELECT 2,101,50,100.2
SELECT * FROM STORE
GO
--