mysql:triggers

参考

http://www.mysqltutorial.org/mysql-triggers.aspx

mysql:triggers

被预先编写并存储在表中的程序， 该程序被特定的 events触发， 比如 对表的 DML 操作
By definition, a trigger or database trigger is a stored program executed automatically to respond to a specific event e.g., insert, update or delete occurred in a table.

被用于： 保护数据的完整性和一致性，
以及 自动 logging 和 审计 等

A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs e.g., insert, update or delete.

trigger vs stored procedure

A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

advantages of Using triggers

1. 提供了一个检查 数据完整性和一致性的方式

2. 可以在数据库内捕捉 业务逻辑的错误

3. 支持 run scheduled task

4, 支持对 数据变更的审查

disadvantages of Using triggers

1. triggers仅能提供一种对数据审查的扩展， 而不能替代所有的审查工作

2. triggers 执行对 clients不可见

3. triggers 加大了mysql server 的负担

triggers 实现：

1. 定义和存储 triggers
   You must use a unique name for each trigger associated with a table

The tablename.TRG file maps the trigger to the corresponding table.
the triggername.TRN file contains the trigger definition.

2. the limitations of triggers

MySQL triggers cannot:

• Use SHOW, LOAD DATA, LOAD TABLE, BACKUP DATABASE, RESTORE, FLUSH and RETURNstatements.
• Use statements that commit or rollback implicitly or explicitly such as COMMIT , ROLLBACK , START TRANSACTION , LOCK/UNLOCK TABLES , ALTER , CREATE , DROP , RENAME.
• Use prepared statements such as PREPAREand EXECUTE.
• Use dynamic SQL statements.

create triggers

定义 tables 用来存放 triggers 获取的数据；

定义 tiggers， associated with a table;

scheduled event

类似于 定时器