当前位置:Gxlcms > mysql > 在MySQL中使用触发器Trigger的操作过程

在MySQL中使用触发器Trigger的操作过程

时间:2021-07-01 10:21:17 帮助过:29人阅读

想监控Bugzilla数据库中几个重要的表,如果它们发生了任何改变(增、删、改),都希望能够记录下来,以便后面再写程序来分析。很自然,就想到使用MySQL的触发器(Trigger)了,学习了一会,记录如下: 1. 先建立一个新的表用于记录我需要的变化: CREATE TAB

想监控Bugzilla数据库中几个重要的表,如果它们发生了任何改变(增、删、改),都希望能够记录下来,以便后面再写程序来分析。很自然,就想到使用MySQL的触发器(Trigger)了,学习了一会,记录如下:
1. 先建立一个新的表用于记录我需要的变化:

CREATE TABLE `bugzilla_log` (
  `id` INT UNSIGNED NOT NULL,
  `table` varchar(80) NOT NULL,
  `action` ENUM('insert','update','delete'),
  `ts` TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='logging some important changes in bugzilla DB';


2. 针对需要监控的表,创建触发器:

CREATE
    TRIGGER `classifications_insert`
    AFTER INSERT
    ON classifications FOR EACH ROW
    INSERT INTO `bugzilla_log` (`id`, `table`, `action`, `ts`) VALUES (NEW.id, 'classifications', 'insert', NOW()); 
?
CREATE
    TRIGGER `classifications_update`
    AFTER UPDATE
    ON classifications FOR EACH ROW
    INSERT INTO `bugzilla_log` (`id`, `table`, `action`, `ts`) VALUES (NEW.id, 'classifications', 'update', NOW()); 
?
CREATE
    TRIGGER `classifications_delete`
    BEFORE DELETE
    ON classifications FOR EACH ROW
    INSERT INTO `bugzilla_log` (`id`, `table`, `action`, `ts`) VALUES (OLD.id, 'classifications', 'delete', NOW());

请注意其中AFTER和BEFORE,以及OLD和NEW的使用。
Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

3. 查看当前数据库中的触发器:

SHOW TRIGGERS;

参考资料:

http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

http://www.jicdesign.com/blog/web-development/how-to-use-mysql-triggers-to-log-table-changes.html

Original article: 在MySQL中使用触发器Trigger的操作过程

©2014 笑遍世界. All Rights Reserved.

人气教程排行