MySQL-触发器

# 插入前 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW 3 BEGIN 4 ... 5 END 6 7 # 插入后 8 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW 9 BEGIN 10 ... 11 END 12 13 # 删除前 14 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW 15 BEGIN 16 ... 17 END 18 19 # 删除后 20 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW 21 BEGIN 22 ... 23 END 24 25 # 更新前 26 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW 27 BEGIN 28 ... 29 END 30 31 # 更新后 32 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW 33 BEGIN 34 ... 35 END 创建触发器

1 delimiter //
2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
3 BEGIN
4 IF NEW. NAME == ‘alex‘ THEN
5     INSERT INTO tb2 (NAME)
6 VALUES
7     (‘aa‘)
8 END//
9 delimiter ;

示例:insert之前

 1 delimiter //
 2 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4     IF NEW. num = 666 THEN
 5         INSERT INTO tb2 (NAME)
 6         VALUES
 7             (‘666‘),
 8             (‘666‘) ;
 9     ELSEIF NEW. num = 555 THEN
10         INSERT INTO tb2 (NAME)
11         VALUES
12             (‘555‘),
13             (‘555‘) ;
14     END IF;
15 END//
16 delimiter ;

示例:insert之后

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

2.删除触发器

DROP TRIGGER tri_after_insert_tb1;

3.使用触发器:一旦对已经生成触发器的数据表进行触发条件操作,就会执行触发器了

insert into tb1(num) values(666)

 

MySQL-触发器

标签：语法   gif   date   isp   drop   weight   lap   src   log