如何创建一套银行客户管理系统?(二):创建示例数据库

[复制链接]
查看950 | 回复0 | 2022-12-20 00:57:21 | 显示全部楼层 |阅读模式
触发器(Trigger)是 MySQL 中非常实用的一个功能,它可以在操作者对表进行「增删改」 之前(或之后)被触发,自动执行一段事先写好的 SQL 代码。

本教程带领大家在实践中学习,你将学到触发器在实际应用场景中的重要应用。

在这个教程中,你是「卡拉云银行」的程序员,你正在搭建一套银行客户管理系统。在这套系统中,你需要设置在INSERT 表之前检测操作者是否输入错误数据、在 UPDATE 时,记录操作者的行为 log ,以及在DELETE 时,判断删除的信息是否符合删除规则。 这三类操作都可以使用 MySQL 触发器来实现。

如果你正在使用触发器搭建一套企业内部工具,我推荐你试试卡拉云,详情见文末。

本教程将带你一起实践的案例

先决条件

在开始之前,请确保您具备以下条件:

创建示例数据库

我们先创建一个干净的示例数据库,方便大家可以跟随本教程一起实践。我们会在这个数据库中演示 MySQL 触发器的多种工作方式。

首先,以 root 身份登录到你的 MySQL 服务器:

<p><pre class="language-sql">    <code class="language-sql">mysql <span class="token operator">-</span>u root <span class="token operator">-</span>p</code></pre></p>
出现提示时,请输入你 MySQL root 账号的密码,然后点击 ENTER 继续。看到 mysql> 提示后,运行以下命令,创建 demo_kalacloud 数据库:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">database</span> demo_kalacloud<span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.00 sec)</code></pre></p>
接下来,切换到新建的 demo_kalacloud 数据库:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">USE</span> demo_kalacloud<span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Database changed</code></pre></p>
接着创建一个 customers 表。我们使用这个表记录银行客户的信息。这个表包括 customer_id,customer_name,和level。咱们先把客户分为两个级别:BASIC和VIP。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">create</span> <span class="token keyword">table</span> customers<span class="token punctuation">(</span>
customer_id <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
customer_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">level</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 0 rows affected (0.01 sec)</code></pre></p>
接着,我们向 customers 表中添加一些客户记录。

<p><pre class="language-sql">    <code class="language-sql">
<span class="token keyword">Insert</span> <span class="token keyword">into</span> customers <span class="token punctuation">(</span>customer_id<span class="token punctuation">,</span> customer_name<span class="token punctuation">,</span> <span class="token keyword">level</span> <span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;1&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;Jack Ma&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;BASIC&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">Insert</span> <span class="token keyword">into</span> customers <span class="token punctuation">(</span>customer_id<span class="token punctuation">,</span> customer_name<span class="token punctuation">,</span> <span class="token keyword">level</span> <span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;2&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;Robin Li&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;BASIC&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">Insert</span> <span class="token keyword">into</span> customers <span class="token punctuation">(</span>customer_id<span class="token punctuation">,</span> customer_name<span class="token punctuation">,</span> <span class="token keyword">level</span> <span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;3&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;Pony Ma&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;VIP&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre></p>
分别运行三个 INSERT 命令后,命令行输出成功信息。

<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.01 sec)</code></pre></p>
我们使用 SELECT 检查一下三条信息是否已经写入表中:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Select</span> <span class="token operator">*</span> <span class="token keyword">from</span> customers<span class="token punctuation">;</span></code></pre></p>
下面我们创建另一个表customer_status,用于保存 customers 表中客户的备注信息。

这个表包含 customer_id 和 status_notes 字段:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Create</span> <span class="token keyword">table</span> customer_status<span class="token punctuation">(</span>customer_id <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> status_notes <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span><span class="token punctuation">;</span></code></pre></p>
然后,我们再创建一个 sales 表,这个表与 customer_id 关联。保存与客户有关的销售数据。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Create</span> <span class="token keyword">table</span> sales<span class="token punctuation">(</span>sales_id <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> customer_id <span class="token keyword">BIGINT</span><span class="token punctuation">,</span> sales_amount <span class="token keyword">DOUBLE</span> <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 0 rows affected (0.01 sec)</code></pre></p>
最后一步,我们再建一个 audit_log 表,用来记录操作员操作「卡拉云银行」客户管理系统时的操作行为。方便管理员在发生问题时,有 log 可查。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Create</span> <span class="token keyword">table</span> audit_log<span class="token punctuation">(</span>log_id <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> sales_id <span class="token keyword">BIGINT</span><span class="token punctuation">,</span> previous_amount <span class="token keyword">DOUBLE</span><span class="token punctuation">,</span> new_amount <span class="token keyword">DOUBLE</span><span class="token punctuation">,</span> updated_by <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> updated_on <span class="token keyword">DATETIME</span> <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 0 rows affected (0.02 sec)</code></pre></p>
至此,你作为「卡拉云银行」的程序员,已经把客户管理系统的demo_kalacloud 数据库和四张表建立完成。接下来,我们将对这个管理系统的关键节点增加对应的触发器。

扩展阅读:《如何使用 MySQL 慢查询日志进行性能优化 - Profiling、mysqldumpslow 实例详解》

1.BEFORE INSERT 触发器使用方法

作为严谨的银行客户管理系统,对任何写入系统的数据都应该提前检测,以防止错误的信息被写进去。

在写入前检测数据这个功能,我们可以使用BEFORE INSERT 触发器来实现。

在操作者对 sales 表中的sales_amount 字段进行写操作时,系统将在写入(INSERT)前检查数据是否符合规范。

我们先来看一下,创建触发器的基本语法。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> <span class="token punctuation">[</span>触发器的名字<span class="token punctuation">]</span>
<span class="token punctuation">[</span>触发器执行时机<span class="token punctuation">]</span> <span class="token punctuation">[</span>触发器监测的对象<span class="token punctuation">]</span>
<span class="token keyword">ON</span> <span class="token punctuation">[</span>表名<span class="token punctuation">]</span>


<span class="token keyword">FOR EACH ROW</span> <span class="token punctuation">[</span>触发器主体代码<span class="token punctuation">]</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
触发器的结构包括:

注:在创建触发器主体时,还可以使用OLD和NEW 来获取 SQL 执行INSERT,UPDATE和DELETE 操作前后的写入数据。这里没看明白没关系,我们将会在接下来的实践中,展开讲解。

讲到这里,大家看了一大堆云里雾里的概念,如果没看懂,也别担心。接下来进入实践环节,只要跟着贴代码看返回结果,很快你就能够通透理解触发器了。

现在,我们来创建第一个触发器,BEFORE INSERT (在执行 insert 之前,执行触发器)。这个触发器用于监测操作者在写入 sales 表中的 sales_amount 值时,这个值是否大于 10000 ,如果大于,那么返回错误信息进行报错。

登录 MySQL Server 后,我们创建一个触发器:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> validate_sales_amount
BEFORE <span class="token keyword">INSERT</span>
<span class="token keyword">ON</span> sales
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">IF</span> NEW<span class="token punctuation">.</span>sales_amount<span class="token operator">></span><span class="token number">10000</span> <span class="token keyword">THEN</span>
SIGNAL SQLSTATE <span class="token string">&#x27;45000&#x27;</span>
<span class="token keyword">SET</span> MESSAGE_TEXT <span class="token operator">=</span> <span class="token string">"你输入的销售总额超过 10000 元。"</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
上面这段代码中,我们使用IF...THEN...END IF 来创建一个监测 INSERT 语句写入的值是否在限定的范围内的触发器。

这个触发器的功能时监测 INSERT 在写入sales_amount 值时,这个新增的(NEW)值是否符合条件( > 10000)。

当操作员录入一个超过 10000 的数字,会返回如下错误信息:

<p><pre class="language-text">    <code class="language-text">SIGNAL SQLSTATE &#x27;45000&#x27;
SET MESSAGE_TEXT = &#x27;你输入的销售总额超过 10000 元。&#x27;;</code></pre></p>
我们来试试看,看看触发器是否已启用。

我们向 sales_amount 中插入一条 11000 的值。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Insert</span> <span class="token keyword">into</span> sales<span class="token punctuation">(</span>sales_id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> sales_amount<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;1&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;1&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;11000&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre></p>
命令行返回错误信息,这就是我们刚刚创建触发器时,填入的错误信息。与我们的设置一致。

下面我们 insert 一个值小于 10000 的数字:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Insert</span> <span class="token keyword">into</span>  sales<span class="token punctuation">(</span>sales_id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> sales_amount<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;1&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;1&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;7700&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre></p>
输入值为 7700 小于设定的 10000  ,insert 命令执行成功。

<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.01 sec)</code></pre></p>
我们调出 sales 表,看看是否插入成功:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Select</span> <span class="token operator">*</span> <span class="token keyword">from</span> sales<span class="token punctuation">;</span></code></pre></p>
确认数据在表中:

通过这张表,我们可以看到,7700 已经插入到表中。

刚刚我们演示了在执行 insert 命令前,检测某个值是否符合设定,接着我们来看在执行 insert 之后,使用触发器将不同的值保存到不同的表中。

扩展阅读:《如何在两台服务器之间迁移 MySQL 数据库 阿里云迁移案例》

连接数据库后需要开发后台系统?

试试卡拉云,拖拽组件连接 API 和数据库直接生成后台系统,两个月的工期降低至1天

立即试用

2.AFTER INSERT触发器使用方法

接着我们讲解 AFTER INSERT ,触发器在监测到我们成功执行了 INSERT 命令后,再执行触发器中设置好的代码。

例如:在银行账户系统中,当我们新建一个账户后,我们将创建成功信息写入对应的 customer_status 表中。

在这个案例中,你作为「卡拉云银行」的程序员,现在要创建一个AFTER INSERT触发器,在创建新客户账户后,将成功信息写入customer_status 表中

要创建AFTER INSERT触发器,请输入以下命令:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> customer_status_records
<span class="token keyword">AFTER</span> <span class="token keyword">INSERT</span>
<span class="token keyword">ON</span> customers
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">Insert</span> <span class="token keyword">into</span> customer_status<span class="token punctuation">(</span>customer_id<span class="token punctuation">,</span> status_notes<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>NEW<span class="token punctuation">.</span>customer_id<span class="token punctuation">,</span> <span class="token string">&#x27;账户创建成功&#x27;</span><span class="token punctuation">)</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 0 rows affected (0.00 sec)</code></pre></p>
这个触发器在操作者向 customers 表中 INSERT 新客户信息后,再向 customer_status 表对应的行中写入成功信息。

现在我们 INSERT 一条信息,看看触发器是否已启用:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Insert</span> <span class="token keyword">into</span> customers <span class="token punctuation">(</span>customer_id<span class="token punctuation">,</span> customer_name<span class="token punctuation">,</span> <span class="token keyword">level</span> <span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;4&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;Xing Wang&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;VIP&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.01 sec)</code></pre></p>
记录 INSERT 成功,接着我们来检查customer_status表中是否写入了对应的成功数据。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Select</span> <span class="token operator">*</span> <span class="token keyword">from</span> customer_status<span class="token punctuation">;</span></code></pre></p>
这里可以看到,我们向 customers  表插入了一个customer_id 为 4 的新用户 ,随后,触发器根据代码自动向customer_status 表中也插入了一个 customer_id 为 4 的开户成功信息。

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

到这里,触发器在INSERT执行前、后的应用,我们已经讲完了,接着我们来讲 UPDATE 触发器。

扩展阅读:《MySQL 配置文件 my.cnf / my.ini 逐行详解》

3.BEFORE UPDATE触发器使用方法

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

刚刚我们创建示例数据库时,创建了两个级别的客户,VIP 和 BASIC 级别。卡拉云银行的客户一旦升级至 VIP,就不能再降级至 BASIC 级别了。

我们使用 BEFORE UPDATE 来贯彻这一规则,这个触发器将在 UPDATE 语句执行之前,先判断是否为降级行为,如果是,则输出报错信息。

我们来创建这个触发器:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> validate_customer_level
BEFORE <span class="token keyword">UPDATE</span>
<span class="token keyword">ON</span> customers
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">IF</span> OLD<span class="token punctuation">.</span><span class="token keyword">level</span><span class="token operator">=</span><span class="token string">&#x27;VIP&#x27;</span> <span class="token keyword">THEN</span>
SIGNAL SQLSTATE <span class="token string">&#x27;45000&#x27;</span>
<span class="token keyword">SET</span> MESSAGE_TEXT <span class="token operator">=</span> <span class="token string">&#x27;VIP 级别客户不能降级为普通级别客户&#x27;</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">IF</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
我们可以使用 OLD 来获取执行 UPDATE 命令前,客户的 level 值。同样,我们使用该IF...THEN...END IF语句来对 level 值是否符合规则进行判断。

我们先来查看一下 customers 表中的数据。

<p><pre class="language-bash">    <code class="language-bash"><span class="token keyword">select</span> * from customers<span class="token punctuation">;</span></code></pre></p>
好,我们选一个已经是 VIP 级别的客户,对他进行降级操作,看看我们的触发器是否能够正确执行。

接下来,运行以下 SQL 命令,试试能不能将 customer_id 为 3 的 VIP 客户降级成 BASIC 客户:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Update</span> customers <span class="token keyword">set</span> <span class="token keyword">level</span><span class="token operator">=</span><span class="token string">&#x27;BASIC&#x27;</span> <span class="token keyword">where</span> customer_id<span class="token operator">=</span><span class="token string">&#x27;3&#x27;</span><span class="token punctuation">;</span></code></pre></p>
执行代码后,命令行返回错误信息:

这说明我们刚刚设置的触发器已经起作用了。

接着我们来试试,对一个BASIC级别的客户运行相同的命令,看看能不能把他升级到VIP级别:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Update</span> customers <span class="token keyword">set</span> <span class="token keyword">level</span><span class="token operator">=</span><span class="token string">&#x27;VIP&#x27;</span> <span class="token keyword">where</span> customer_id<span class="token operator">=</span><span class="token string">&#x27;2&#x27;</span><span class="token punctuation">;</span></code></pre></p>
执行成功:

<p><pre class="language-text">    <code class="language-text">Output
Rows matched: 1  Changed: 1  Warnings: 0</code></pre></p>
我们再来看一下 customers 表中的数据情况:

<p><pre class="language-bash">    <code class="language-bash"><span class="token keyword">select</span> * from customers<span class="token punctuation">;</span></code></pre></p>
可以看到刚才 customer_id 为 2 的 BASIC  客户已经升级为 VIP 客户。

BEFORE UPDATE 触发器用于在更新数据前进行确认,很好的守护了系统的业务规则。接着我们来看看 AFTER UPDATE 在客户管理系统中的应用。

扩展阅读:《MySQL Workbench 操作 MySQL / MariaDB 数据库中文教程》

4.AFTER INSERT 触发器使用方法

本节我们来演示 AFTER UPDATE 在实际中的应用。AFTER UPDATE 多用于 log 记录,在管理系统多操作者使用的环境中,管理员需要设置操作 log 记录,以便在出问题时,可以查看操作者对表编辑的操作,可追根溯源。

我们先来创建一个对 sales 表操作的 log 记录触发器。

当操作者对  sales 表进行修改后,操作记录会被写入 audit_log 表中。

触发器将监测用户 ID 、更新前的销售总额、更新后的销售总额、操作者 ID、修改时间等信息,作为 log 存入 audit_log 表中。

使用以下命令建立这个 log 记录触发器:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> log_sales_updates
<span class="token keyword">AFTER</span> <span class="token keyword">UPDATE</span>
<span class="token keyword">ON</span> sales
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">Insert</span> <span class="token keyword">into</span> audit_log<span class="token punctuation">(</span>sales_id<span class="token punctuation">,</span> previous_amount<span class="token punctuation">,</span> new_amount<span class="token punctuation">,</span> updated_by<span class="token punctuation">,</span> updated_on<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>NEW<span class="token punctuation">.</span>sales_id<span class="token punctuation">,</span>OLD<span class="token punctuation">.</span>sales_amount<span class="token punctuation">,</span> NEW<span class="token punctuation">.</span>sales_amount<span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token keyword">USER</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
当操作者对 sales 表中的一条客户信息进行 UPDATE 操作时,触发器会在UPDATE操作之后,将操作行为记录在 audit_log 中。包括 sales_id ,修改 sales_amount 值的前后变化。

销售总额的变化是审计的关键数据,所以要把它记录在 audit_log 中。使用OLD 来获取更新前的 sales_amount 值,使用 NEW 来获取更新后的值。

另外我们还要记录修改 sales 表的操作者信息及操作时间。

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

为了测试这个触发器,我们先在 sales 表中创建一条信息记录:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Insert</span> <span class="token keyword">into</span> sales<span class="token punctuation">(</span>sales_id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> sales_amount<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#x27;5&#x27;</span><span class="token punctuation">,</span> <span class="token string">&#x27;2&#x27;</span><span class="token punctuation">,</span><span class="token string">&#x27;8000&#x27;</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.00 sec)</code></pre></p>
接下来,我们来更新这条记录:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Update</span> sales <span class="token keyword">set</span> sales_amount<span class="token operator">=</span><span class="token string">&#x27;9000&#x27;</span> <span class="token keyword">where</span> sales_id<span class="token operator">=</span><span class="token string">&#x27;5&#x27;</span><span class="token punctuation">;</span></code></pre></p>
您将看到以下输出:

<p><pre class="language-text">    <code class="language-text">Output
Rows matched: 1  Changed: 1  Warnings: 0</code></pre></p>
理论上,我们更新了 sales 表后,触发器应该触发了操作,将我们刚刚的修改记录到了audit_log 表中。我们用以下命令,看看audit_log 表中是否已经有记录了。

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Select</span> <span class="token operator">*</span> <span class="token keyword">from</span> audit_log<span class="token punctuation">;</span></code></pre></p>
如下表,触发器更新了audit_log 表,表中包含了sales_amount 更新前的旧值和更新后的新值。

至此,使用 AFTER UPDATE 制作的 log 自动记录触发器就完成了。

下一节,我们来学习 DELETE 相关的触发器。

扩展阅读:《如何查看 MySQL 数据库、表、索引容量大小?找到占用空间最大的表》

5.BEFORE DELETE触发器使用方法

BEFORE DELETE触发器会在DELETE语句执行之前调用。

这些类型的触发器通常用于在不同的相关表上强制执行参照完整性。

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

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

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

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> validate_related_records
BEFORE <span class="token keyword">DELETE</span>
<span class="token keyword">ON</span> customers
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">IF</span> OLD<span class="token punctuation">.</span>customer_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> customer_id <span class="token keyword">from</span> sales<span class="token punctuation">)</span> <span class="token keyword">THEN</span>
SIGNAL SQLSTATE <span class="token string">&#x27;45000&#x27;</span>
<span class="token keyword">SET</span> MESSAGE_TEXT <span class="token operator">=</span> <span class="token string">&#x27;这位客户有相关联的销售记录,不能删除。&#x27;</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
现在,我们试着删除有销售关联信息的客户:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Delete</span> <span class="token keyword">from</span> customers <span class="token keyword">where</span> customer_id<span class="token operator">=</span><span class="token string">&#x27;2&#x27;</span><span class="token punctuation">;</span></code></pre></p>
所以,你会看到以下输出:

这个触发器做到了先检测 sales 是否与正要被删除的 customers 表中的数据有关联,防止有关联信息的数据被误删除。

不过有时候,我们需要删除主数据后,再让系统自动帮我们删除与之相关联的其他所有数据。这时,我们就要用到 AFTER DELETE 这个触发器了。

扩展阅读:《在 MySQL 中 DATETIME 和 TIMESTAMP 时间类型的区别及使用场景 - 实战案例讲解》

6.AFTER DELETE触发器使用方法

接着说说 AFTER DELETE ,一旦记录被成功删除,这个触发器就会被激活。

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

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

我们来看一下这个触发器如何创建:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> delete_related_info
<span class="token keyword">AFTER</span> <span class="token keyword">DELETE</span>
<span class="token keyword">ON</span> sales
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">Delete</span> <span class="token keyword">from</span> customers <span class="token keyword">where</span> customer_id<span class="token operator">=</span>OLD<span class="token punctuation">.</span>customer_id<span class="token punctuation">;</span><span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre></p>
接下来,我们来试试这个触发器。删除销售记录中 customer_id 为 2 的销售记录:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Delete</span> <span class="token keyword">from</span> sales <span class="token keyword">where</span> customer_id<span class="token operator">=</span><span class="token string">&#x27;2&#x27;</span><span class="token punctuation">;</span></code></pre></p>
<p><pre class="language-text">    <code class="language-text">Output
Query OK, 1 row affected (0.00 sec)</code></pre></p>
接着我们检查以下 customers 表中的关联信息是否一起自动删除:

<p><pre class="language-sql">    <code class="language-sql"><span class="token keyword">Select</span> <span class="token operator">*</span> <span class="token keyword">from</span> customers <span class="token keyword">where</span> customer_id<span class="token operator">=</span><span class="token string">&#x27;2&#x27;</span><span class="token punctuation">;</span></code></pre></p>
命令行会返回 Empty Set 的结果,我们刚刚删除了 sales 表中的信息后,customers 表中的关联信息也被一起删除了。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则