【Mysql】触发器

触发器

操作前触发

CREATE TRIGGER tigger_table1 BERFORE inserton tb1 for each row

BEGIN

              …

END

 

操作后触发

CREATE TRIGGER tigger_table1 AFTER inserton tb1 for each row

BEGIN

              …

END

 

# 准备表

CREATE TABLE cmd(

       id INT PRIMARY KEY auto_increment,

       user CHAR(32),

       priv CHAR(10),

       cmd CHAR(64),

       sub_time datetime, # 提交时间

       success enum(‘yes’, ‘no’)  # no代表执行失败

);

 

CREATE TABLE errlog(

       id INT PRIMARY KEY auto_increment,

       err_cmd CHAR(64),

       err_time datetime,   # 发送错误时间

);

 

# 创建触发器

delimiter //     # 声明结束符号

CREATE TRIGGER tri_after_cmd AFTER INSERT ON cmd FOR EACH ROW

BEGIN

       IF NEW.success = ‘no’ THEN  # 如果执行失败,插入到错误日志

                     INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub.time);

       END IF;

END//

delimiter;    # 还原结束符号

 

# 执行阶段

INSERT INTO cmd VALUES

(‘CAYA1’, ‘FDSF’, ‘FSDF’, NOW(), ‘yes’),

(‘CAYA2’, ‘FDSF’, ‘FSDF’, NOW(), ‘no’),

(‘CAYA3’, ‘FDSF’, ‘FSDF’, NOW(), ‘no’),