mysql 触发器使用场景_mysql三个应用场景

//创建表

DROP TABLE IF EXISTS updatelog;CREATE TABLE `updatelog` (

`id` int(11) NOT NULL AUTO_INCREMENT,`resourceid` int(11) DEFAULT NULL,`log` text,`createtime` datetime DEFAULT NULL,PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

//必须指定主键或unique，不然无法replace

DROP TABLE IF EXISTS reslastlog;CREATE TABLE `reslastlog` (

`resourceid` int(11) NOT NULL DEFAULT '0',`log` text,`updatetime` datetime DEFAULT NULL,PRIMARY KEY (`resourceid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

//创建触发器

DROP TRIGGER IF EXISTS t_afterinsert_on_updatelog;delimiter //

CREATE TRIGGER t_afterinsert_on_updatelog

AFTER INSERT ON updatelog

FOR EACH ROW

BEGIN

replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);END;//

delimiter;

DROP TRIGGER IF EXISTS t_afterdelete_on_updatelog;delimiter //

CREATE TRIGGER t_afterdelete_on_updatelog

AFTER DELETE ON updatelog

FOR EACH ROW

BEGIN

delete from reslastlog where resourceid=old.resourceid;END;//

delimiter;

//测试

insert into updatelog(resourceid, log, createtime) values(1, "version 1-0",now());insert into updatelog(resourceid, log, createtime) values(1, "version 1-1",now());insert into updatelog(resourceid, log, createtime) values(2, "version 2-2",now());delete from updatelog where resourceid = 2;//触发器相关操作

mysql> show triggers;+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+

| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |

+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+

| t_afterinsert_on_updatelog | INSERT | updatelog | BEGIN

replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);END | AFTER | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |

| t_afterdelete_on_updatelog | DELETE | updatelog | BEGIN

delete from reslastlog where resouceid=old.resourceid;END | AFTER | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |

+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+2 rows in set (0.00sec)

drop trigger t_afterinsert_on_updatelog;