mysql触发器

-- mysql触发器
触发器是想要某条语句或某些语句在事件发生时自动执行，而执行的时间可以在"事件发生"之前或之后。
创建触发器
需要唯一的触发器名；触发器关联的表（即该表被执行某些语句而引发触发器发生);触发器应该响应的活动（增删改查）；
触发器何时执行（事件发生之前或之后）；

一个简单的例子：
CREATE TABLE writer (
    id INT,
    content VARCHAR(255)
    );

CREATE TRIGGER tri_1 AFTER INSERT ON writer
FOR EACH ROW SELECT 'new add' INTO @b;

DROP TRIGGER tri_1;
INSERT INTO writer VALUES (3, 'hhhh');
SELECT @b; -- 返回 new add

分析：
CREATE TRIGGER 来定义一个触发器， 后面跟触发器的名字。
AFTER/BEFORE 指定触发器是在事件执行之前发生还是之后发生，这里 AFTER 是代表之后。
INSERT 是代表 表writer发生了 INSERT 操作，会引起触发器的发生。
ON writer， ON 后面跟触发器关联的表名。
FOR EACH ROW 是指writer表对于每一个新插入的行都会引起触发器发生。
SELECT 'new add' INTO @b 即为触发器的内容。对于只有一行语句可以这么写，而多行语句使用 BEGIN 和 END

注：在mysql5.0之后触发器不能直接返回内容，即触发器语句里不能包含select,例如上面不能直接写select 'new add'
会产生"not allowed to return a result set from a trigger"，因此定义一个变量，将内容导入到变量里。

只有表支持触发器，视图和临时表都不支持。而且每个表最多只能定义6个触发器，分别为 INSERT BEFORE, INSERT AFTER,
UPDATE BEFORE, UPDATE AFTER, DELETE BEFORE, DELETE AFTER 也就是每个表每个事件每次只允许定义一个触发器。

删除触发器：DROP TRIGGER 触发器名
触发器不能更新或覆盖，为了修改一个触发器，必须先删除在重建。

INSERT 触发器
① 在触发器代码里可以引用一个名字为 NEW 的虚拟表，来访问被插入的行。
②在 BEFORE INSERT 触发器中， NEW 的值是可以被更新的， 也就是允许更新即将被插入的值。通常用于数据净化。
③对于 AUTO_INCREMENT 的列， NEW 在 INSERT 执行之前包含0， 在 INSERT 执行之后包含新的自动生成值。

DELETE 触发器
①在 DELETE 触发器代码内， 可以引用一个名为 OLD 的虚拟表，访问被删除的行。
② OLD 的值只能读不能更新。

UPDATE 触发器
①在 UPDATE 触发器代码中， 可以引用一个名为 OLD 的虚拟表来访问以前（update语句前）的值，引用一个名为 NEW 的
虚拟表访问新更新的值。
② 在 BEFORE UPDATE 触发器中， NEW 中的值允许被更新。
③ OLD 表中的值全部都是只读，不能更新。
 

examples:

-- 创建db7数据库
CREATE DATABASE db7;

-- 使用db7数据库
USE db7;

-- 创建账户表account
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 账户id
    NAME VARCHAR(20),            -- 姓名
    money DOUBLE                -- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);


-- 创建日志表account_log
CREATE TABLE account_log(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 日志id
    operation VARCHAR(20),            -- 操作类型 (insert update delete)
    operation_time DATETIME,        -- 操作时间
    operation_id INT,            -- 操作表的id
    operation_params VARCHAR(200)           -- 操作参数
);

DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 向account表添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);

-- 查询account表
SELECT * FROM account;

-- 查询account_log表
SELECT * FROM account_log;

DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('更新前{id=',old.id,',name=',old.name,',money=',old.money,'}','更新后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;


-- 修改account表中李四的金额为2000
UPDATE account SET money=2000 WHERE id=2;

-- 查询account表
SELECT * FROM account;

-- 查询account_log表
SELECT * FROM account_log;

DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;

-- 删除account表中王五
DELETE FROM account WHERE id=3;

-- 查询account表
SELECT * FROM account;

-- 查询account_log表
SELECT * FROM account_log;