MySQL触发器

触发器

1.1 介绍

触发器：是指与表结构有关的数据库对象，指在insert、update、delete之前或之后，触发并执行触发器中定义的SQL语句集合。

触发器的特性：可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名old 和 new 来引用触发器中发生变化的记录。

触发器类型	new和old的使用
insert型触发器	new表示将要或者已经新增的数据
update型触发器	old表示修改之前的数据，new表示将要或者已经修改后的数据
delete型触发器	old表示将要或者已经删除的数据

1.2 创建触发器

语法结构

delimiter $$
create trigger`pro_db`.`trigger_users` before/after insert/update/delete
    ON 触发器关联的表名
    for each row begin --行级触发器
		trigger_stmt;
    end$$
delimiter ;

mysql只支持行级触发器，oracle不仅支持行，而且支持语句级触发器。

1.3 示例需求

通过触发器记录，users表的数据变更日志，包含增加，修改，删除；

CREATE TABLE users(
	uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(64),
	PASSWORD VARCHAR(64)
)

CREATE TABLE users_logs(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	operation VARCHAR(20) NOT NULL COMMENT '操作类型，insert、update、delete',
	operation_time DATETIME NOT NULL COMMENT '操作时间',
	operation_id INT NOT NULL COMMENT '操作表id',
	operation_params VARCHAR(500) COMMENT '操作参数'	
)
-- 插入触发器
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `pro_db`.`users_insert` AFTER INSERT
    ON `pro_db`.`users`
    FOR EACH ROW BEGIN
	INSERT INTO users_logs(id,operation,operation_time,operation_id,operation_params) 
	VALUES (NULL,'INSERT',NOW(),new.uid,CONCAT('(插入后uid:',new.uid,'username:',new.username,'password:',new.password,')'));
    END$$
DELIMITER ;

-- 删除触发器
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `pro_db`.`users_delete` AFTER DELETE
    ON `pro_db`.`users`
    FOR EACH ROW BEGIN
	INSERT INTO users_logs(id,operation,operation_time,operation_id,operation_params) 
	VALUES (NULL,'DELETE',NOW(),old.uid,CONCAT('(删除的数据uid:',old.uid,'username:',old.username,'password:',old.password,')'));
    END$$
DELIMITER ;

-- 修改触发器
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `pro_db`.`users_update` AFTER UPDATE
    ON `pro_db`.`users`
    FOR EACH ROW BEGIN
	INSERT INTO users_logs(id,operation,operation_time,operation_id,operation_params) 
	VALUES (NULL,'update',NOW(),old.uid,CONCAT(
	'(修改前uid:',old.uid,'username:',old.username,'PASSWORD:',old.password,')',
	'(修改后uid:',new.uid,'username:',new.username,'password:',new.password,')'));
    END$$
DELIMITER ;


INSERT INTO users VALUE(NULL,'zhangsan','123');
INSERT INTO users VALUE(NULL,'lisi','456');

UPDATE users SET PASSWORD='123' WHERE username='lisi';
DELETE FROM users WHERE username='lisi';

1.3 删除触发器

语法结构：

drop trigger 触发器名字
DROP TRIGGER users_update;

如果没有指定表名，默认为当前数据库。

1.4 查看触发器

可以通过执行show triggers命令查看触发器的状态，语法等信息。

show triggers;