根据触发器和存储过程更新摘要表 [英] Updating the summary table based on triggers and stored procedures

问题描述

我有一个典型的基于LAMP的站点+ Zend Framework，其中有一个基本表和一个摘要表.摘要表用于显示报告中的数据.

I have a typical LAMP based site + Zend Framework where I have a base table and a summary table. Summary table is used to display data in reports.

基本表-

ID | Status
1 | 1
2 | 1
3 | 2
4 | 2
5 | 1
6 | 1

摘要表-

Status | Count
1 | 4
2 | 2

基本表将平均每天进行20次更改(插入，更新，删除).

The base table will be changed(insert,update,delete) at an average of 20 times per day.

当前，我正在使用触发器来调用存储过程，该存储过程将基于基表来更新摘要表.

Currently, I am using triggers to call a stored procedure which will update the summary table based on the base table.

这是存储过程.

CREATE PROCEDURE UpdateSummary()

BEGIN

UPDATE summary a

INNER JOIN

(SELECT status, count(*) c from base group by status) b

ON a.status = b.status

SET a.count = b.c;

END

我有3个触发器(每个触发器-插入，删除和更新).我在下面仅显示了插入示例.其他与此类似.

And I have 3 triggers (one for each - Insert, Delete and Update). I have shown the insert sample alone below. Other are similar to this.

CREATE TRIGGER S_T_TRIGGER_I

AFTER INSERT ON base

FOR EACH ROW

CALL UpdateSummary();

我希望汇总表始终更新为最新值 .像这样使用触发器和存储过程是最好的方法，还是有一种优雅的方法?

I want the summary table to be updated to the latest values always. Using triggers and stored procedure like this is the best way or is there a elegant way to do this?

推荐答案

为什么不使用像这样的视图:

Why don't you use a view like :

 CREATE VIEW Summary AS 
      SELECT status, count(*)
      FROM Base
      GROUP BY status;

您每次需要时，只需:

SELECT * 
FROM Summary 

您将实时获得结果(重新计算每个调用).

And you'll get your result in real time (each call re-computed).

可以像在Zend Framework中使用table一样使用视图.只是您需要显式指定主键，如这里

Views can be used the same way like table is used in Zend Framework. Just that you need to specify a primary key explicitly as explained here

这篇关于根据触发器和存储过程更新摘要表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！