MySQL触发器的定义和使用

103 阅读 0 评论 68 点赞

我是靠谱客的博主热心招牌，最近开发中收集的这篇文章主要介绍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 ;

重要语法组成分析：

DELIMITER //：MySQL 默认分隔符是; 但在触发器中，我们使用 // 表示触发器的开始与结束。
FOR EACH ROW：这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。
[触发器主体代码]：这里是当满足触发条件后，被触发执行的代码主体。这里可以是一句 SQL 语句，也可以是多行命令。如果是多行命令，那么这些命令要写在 BEGIN...END 之间。
在创建触发器主体时，还可以使用OLD和NEW 来获取 SQL 执行INSERT，UPDATE和DELETE 操作前后的写入数据。

也即是：
```
`NEW`：代表新插入的数据

`OLD`：代表旧数据  
```

代码示例：

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 语句写入的值是否在限定的范围内
//的触发器，如果不在范围内，则生成一个异常信号并打印提示信息。

运行结果

在这里插入图片描述

2、AFTER INSERT 触发器的使用

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

一般化语法格式：

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

[触发器主体代码]//

DELIMITER ;

代码示例：

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

运行结果：
在这里插入图片描述

说明：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 ;

代码示例：

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 ;

运行结果：
在这里插入图片描述

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 ;

代码示例：

函数介绍：

你可以使用 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;

运行结果：
在这里插入图片描述

思考总结：

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 ;

代码示例：

//触发器定义
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 ;

运行结果：

在这里插入图片描述

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

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

6、AFTER DELETE 触发器的使用

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

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

一般化语法格式：

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

[触发器主体代码]//

DELIMITER ;

代码示例：

//若插入的账户的金额大于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 ;

数据：

在这里插入图片描述

执行语句：

delete from commant_user where user_amount < 20000;

运行结果：

在这里插入图片描述

思考总结：

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

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

7、查看触发器：

（1）直接查看触发器

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

SHOW TRIGGERS;

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

SHOW TRIGGERS G

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

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

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

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

SELECT * FROM information_schema.triggers G

8、删除触发器：

Drop trigger [触发器名称];

后记：

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

最后

以上就是热心招牌为你收集整理的MySQL触发器的定义和使用的全部内容，希望文章能够帮你解决MySQL触发器的定义和使用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：mysql
浏览次数：103 次浏览
发布日期：2023-06-01 06:51:01
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujogf1_14_jcy.html

MySQL触发器的定义和使用

概述

1、BEFORE INSERT触发器的使用

2、AFTER INSERT 触发器的使用

思考总结：

3、BEFORE UPDATE 触发器的使用

4、AFTER UPDATE 触发器的使用

思考总结：

5、BEFORE DELETE 触发器的使用

6、AFTER DELETE 触发器的使用

思考总结：

7、查看触发器：

（1）直接查看触发器

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

8、删除触发器：

后记：

最后

评论列表共有 0 条评论

发表评论取消回复

MySQL触发器的定义和使用

概述

1、BEFORE INSERT触发器的使用

2、AFTER INSERT 触发器的使用

思考总结：

3、BEFORE UPDATE 触发器的使用

4、AFTER UPDATE 触发器的使用

思考总结：

5、BEFORE DELETE 触发器的使用

6、AFTER DELETE 触发器的使用

思考总结：

7、查看触发器：

（1）直接查看触发器

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

8、删除触发器：

后记：

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复