MySQL触发器

一、 使用命令行创建触发器 

trigger_insert 、trigger_update 、trigger_delete

CREATE TRIGGER trigger_insert
     AFTER INSERT ON student FOR EACH ROW
         INSERT INTO log( time, operation, detail )
                    VALUES(    NOW(),'INSERT',CONCAT( '新纪录: ', NEW.id, NEW.name, NEW.gender, NEW.age ));

 CREATE TRIGGER trigger_update
     AFTER UPDATE ON student FOR EACH ROW
         INSERT INTO log( time, operation, detail )
                     VALUES (NOW(),'UPDATE',CONCAT( '(',
                    OLD.id, OLD.name, OLD.gender, OLD.age,')',

             
  CREATE TRIGGER trigger_delete
      AFTER DELETE ON student FOR EACH ROW
          INSERT INTO log( time, operation, detail )
                      VALUES(    NOW(),'DELETE',CONCAT( '旧纪录: ', OLD.id, OLD.name, OLD.gender, OLD.age ));    

1、查看触发器

SHOW TRIGGERS;

2、查看插入触发器的创建语句
SHOW CREATE TRIGGER trigger_insert;

 SHOW CREATE TRIGGER trigger_update;​​​​​​​
SHOW CREATE TRIGGER trigger_delete;

3、测试插入触发器
 
INSERT INTO student(name,gender,age) VALUES('张三', '女', 20);

UPDATE student SET name='宋风',gender='男',age=25 WHERE id=6;

DELETE FROM student WHERE id=6;

4、删除触发器

DROP TRIGGER trigger_insert;

DROP TRIGGER trigger_update;

DROP TRIGGER trigger_delete;

​​​​​​​