MySQL触发器的定义和使用_e5 触发器的定义和应用 一、实验目的: 熟练使用mysql触发器的定义和应用。

---

触发器的作用：

触发器（Trigger）是 MySQL 中实用的一个功能，它可以在操作者对表进行「增删改」 之前（或之后）被触发，自动执行一段事先写好的 SQL 代码。

---

---

触发器的基本分类：

根据触发器的作用时机和触发信号，可以分为以下6种：

• BEFORE INSERT ： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。
• AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。
• BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。
• AFTER UPDATE ：在更新数据后，将操作行为记录在 log 中
• BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。
• AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。

---

---

1、BEFORE INSERT触发器的使用

作为严谨的数据库系统，对任何写入系统的数据都应该提前检测，以防止错误的信息被写进去。在写入前检测数据这个功能，我们可以使用BEFORE INSERT 触发器来实现。

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

重要语法组成分析：

• DELIMITER //：MySQL 默认分隔符是; 但在触发器中，我们使用 // 表示触发器的开始与结束。

• FOR EACH ROW：这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。

• [触发器主体代码]：这里是当满足触发条件后，被触发执行的代码主体。这里可以是一句 SQL 语句，也可以是多行命令。如果是多行命令，那么这些命令要写在 BEGIN...END 之间。

• 在创建触发器主体时，还可以使用OLD和NEW 来获取 SQL 执行INSERT，UPDATE和DELETE 操作前后的写入数据。

  也即是：

  `NEW`：代表新插入的数据
  
  `OLD`：代表旧数据  
  1
  2
  3

代码示例：

delimiter //
create trigger Linit_num
    before insert
    on student
    for each row

    //------------------------------------------------
    if new.Sage > 28 or NEW.Sage < 15  //if ...
    then    //than ...（if语句生效，执行than）
        signal SQLSTATE '45000'
        set MESSAGE_TEXT = '该生年龄异常';
    end if// 
    //------------------------------------------------
       
delimiter;

//上面这段代码中，使用IF...THEN...END IF 来创建一个监测 INSERT 语句写入的值是否在限定的范围内
//的触发器，如果不在范围内，则生成一个异常信号并打印提示信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

运行结果

---

---

2、AFTER INSERT 触发器的使用

after insert 可以用于当我们每成功插入一个元组（行级触发），就做出相应的操作。

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

代码示例：

delimiter //
create trigger ins_after
    after insert
    on student
    for each row
    //--------每成功插入一个新元组，就去执行下面的语句-------------
    
        insert into message value (NEW.Sno,'数据插入成功')//
        
    //------------------------------------------------------
delimiter ;
1
2
3
4
5
6
7
8
9
10
11

运行结果：

说明：AFTER INSERT 特别适合这种状态变更的关联写入操作。比如开户、暂停、注销等各类状态变更。

---

---

思考总结：

insert操作可以根据触发时机生成两个触发器。

inser before一般用于对插入的新数据进行校验。

insert after一般用于数据插入成功后，及时更新其他与该表关系紧密的表。

---

---

3、BEFORE UPDATE 触发器的使用

BEFORE UPDATE触发器与BEFORE INSERT 触发器非常类似，我们可以使用BEFORE UPDATE 触发器在更新数据之前，先做一次业务逻辑检测，避免发生误操作。

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

代码示例：

delimiter //
create trigger upd_bef
    before update
    on student
    for each row

    if OLD.Sdept = 'CS' < 15 then
        signal SQLSTATE '45000'
            set MESSAGE_TEXT = '拒绝更新CS系学生';
    end if //

delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12

运行结果：

---

---

4、AFTER UPDATE 触发器的使用

AFTER UPDATE 多用于 log 记录，在管理系统多操作者使用的环境中，管理员需要设置操作 log 记录，以便在出问题时，可以查看操作者对表编辑的操作，可追根溯源。

当操作者对table表中的一条客户信息进行操作时，触发器会在UPDATE操作之后，将操作行为记录在 table_log 中。包括 table_id ，修改 table_amount 值的前后变化。

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

代码示例：

函数介绍：

你可以使用 SELECT USER() 来检测当前操作用户的账号，用 NOW() 语句抓去当前服务器日期和时间。

//首先，我们建立一个记录student表更新日志的table
create table student_log(
    Sno char(10),           //更新的studentSno
    previous_data char(20), //更新前的内容
    new_data char(20),      //更新后的内容
    previous_amount char(20),//上次更新该元组的用户
    new_amount char(20),     //这更新该元组的用户
    update_by datetime      //时间
);


//建立触发器
delimiter //
create trigger upde_after
    after update
    on student
    for each row
    
    //===============================================================================================
    insert into student_log value (NEW.Sno,OLD.Sage,NEW.Sage,(SELECT USER()),(SELECT USER()),NOW())//
    //===============================================================================================
    
delimiter ;



//测试
update student
set Sage = Sage+1
where Sname = '张立';

select *
from student_log;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

运行结果：

---

---

思考总结：

update操作可以根据触发时机生成两个触发器。

update before一般用于对更新的新数据进行校验或逻辑检测。

update after一般用于数据更新成功后，记录日志。

---

---

5、BEFORE DELETE 触发器的使用

BEFORE DELETE 的应用场景通常是确保有关联的数据不被错误的误删除掉。

例如：sales 表通过customer_id 与customers表相关联（外码）。如果操作者删除了customers 表中的一条数据，那么 sales 表中某些数据就失去了关联线索。

为了避免这种情况的发生，我们需要创建一个 BEFORE DELETE触发器，防止记录被误删除。

---

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

---

代码示例：

//触发器定义
delimiter //
create trigger del_before
    before delete
    on sc
    for each row
    
    //======================================================
    if OLD.Sno in (select student.Sno from student)
    then
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '这位学生有相关联的记录，不能删除。';
    end if //
    //======================================================
    
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---

运行结果：

结束之前。有必要对一个知识点进行讲解。

例如：SC表中的Sno属性来自Student，是一个外码。因此，当我们建立上述的delete触发器的时候，应该在student表上建立，而不是SC表。否则我们的这个触发器的建立是没有太大意义的。

---

---

6、AFTER DELETE 触发器的使用

这个触发器在实际场景用的应用也比较广泛。比如银行系统中的升级降级操作，当客户花掉自己的账户积分后，激活触发器，触发器可以判断剩余积分是否满足客户当前等级，如果不满足，自动做降级操作。

'AFTER DELETE`触发器的另一个用途是在删除主表中的数据后，与这个主表关联的数据，一起自动删除。

---

一般化语法格式：

DELIMITER //
CREATE TRIGGER [触发器的名字]
[BEFORE | AFTER] [insert | select | update]
ON [表名]
FOR EACH ROW 

[触发器主体代码]//

DELIMITER ;
1
2
3
4
5
6
7
8
9

---

代码示例：

//若插入的账户的金额大于10000，将其在插入到VIP Table中
delimiter //
create trigger _commant_user_insert_after_trigger_
    after insert
    on commant_user
    for each row
    if NEW.user_amount > 10000 then
        insert into VIP_Users value (NEW.user_id,NEW.user_name,NEW.user_age,NEW.user_amount);
    end if //
    delimiter ;


//若删除的commant表格中的账户也存在于VIP表格中，将其级联删除
delimiter //
create trigger _commant_user_deleter_after_trigger_
    after delete
    on commant_user
    for each row
    if OLD.user_id in (select VIP_Users.user_id from VIP_Users)
    then
        delete from VIP_Users where VIP_Users.user_id = OLD.user_id;
    end if //
    delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

---

数据：

执行语句：

delete from commant_user where user_amount < 20000;
1

运行结果：

---

---

思考总结：

delete操作可以根据触发时机生成两个触发器。

delete before一般用于保证被删除的元组不会是别的表的依赖。
delete after一般用于数据删除成功后，更新其他Table（系统信息）的状态。

---

---

7、查看触发器：

（1）直接查看触发器

当我们想查看数据库中的触发器有哪些时，可用以下命令：

SHOW TRIGGERS;
1

后面加上 \G 是触发器列表竖排列：

SHOW TRIGGERS \G
1

（2）在 triggers 表中查看触发器信息

在 MySQL Server 中，数据库 information_schema 的 triggers 表中存着所有触发器的信息。所有我们可以通过 SELECT 来查看。

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名称';
1

当然，也可以不指定触发器名称，来查看所有。

SELECT * FROM information_schema.triggers \G
1

8、删除触发器：

Drop trigger [触发器名称];
1

后记：

上面，我们只是简单介绍了行级触发器的基本使用方式。通过上面的学习我们就能感受到触发器作用之广泛。在实际的开发中，我们应巧用、善用触发器使我们的数据库系统更加强大。