教你如何使用MySQL触发器

八卦谈佚名 ▪ 2024-03-29 20:25:20

MySQL 触发器在添加、更新或删除表行时对表应用限制。

MySQL 中的列应用了少量的值限制。例如，将列数据类型设置为tiny int而不是 null需要一个小的数值输入。尽管如此，仍需要更多的限制来保持数据的完整性。

本教程向您展示如何使用 MySQL 触发器并为每种类型的触发器提供示例。

IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 www.xiaolin.cc

先决条件

在数据库服务器上运行 MySQL 的系统
具有 root 权限的MySQL 用户帐户
基本 MySQL 命令的知识（请参阅我们可下载的MySQL 命令备忘单）

MySQL 中的触发器是什么？

触发器是一个命名的 MySQL 对象，它在表中发生事件时激活。触发器是与特定表关联的特定类型的存储过程。

NEW触发器允许使用和访问表中的值以进行比较OLD。修饰符的可用性取决于您使用的触发事件：

在尝试插入数据时检查或修改值使NEW.<column name>修饰符可用。这是因为表格已更新为新内容。相反，OLD.<column name>插入语句的值不存在，因为事先没有信息存在于其位置。

更新表格行时，两个修饰符都可用。有OLD.<colum name>我们想更新为数据的NEW.<column name>数据。

最后，当移除一行数据时，OLD.<column name>修饰符访问移除的值。NEW.<column name>不存在，因为删除时没有任何东西可以替换旧值。

注意：通过学习如何使用某些命令来重命名 MySQL 中的列，可以轻松管

理数据库。

MySQL 触发器示例

作为应用触发器的示例，将新值插入到person表中会产生与原始输入不同的结果：

请注意，插入的名称最初是小写的。选择表格时，第一个字母显示为大写。尽管没有任何与常规插入语句不同的迹象，但触发器在插入语句之前触发以大写名称的第一个字母。

使用 MySQL 触发器

与表关联的每个触发器都具有基于两个因素的唯一名称和功能：

1.时间。BEFORE或AFTER特定的行事件。

2.事件。INSERT,UPDATE或DELETE.

MySQL 触发器根据激活时间和事件触发，总共有六个独特的触发器组合。before 语句有助于在提交之前检查数据并进行更改，而 after 语句首先提交数据然后执行语句。

一组动作的执行自动发生，影响语句中所有插入、删除或更新的行。

创建触发器

使用CREATE TRIGGER语句语法创建新触发器：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>ON <table name>FOR EACH ROW<trigger body>;

最佳做法是使用以下信息命名触发器：

<trigger time>_<table name>_<trigger event>

例如，如果触发器在名为employee的表上插入之前触发，则最好的约定是调用触发器：

before_employee_insert

或者，一种常见的做法是使用以下格式：

<table name>_<first letter of trigger time><first letter of trigger name>

表employee的前插入触发器名称如下所示：

employee_bi

对于受函数影响的每一行，触发器在由<table name>定义的表上发生事件的特定时间执行。

IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦唤鏁版嵁鎭㈠IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦唤鏁版嵁鎭㈠

删除触发器

要删除触发器，请使用以下DROP TRIGGER语句：

DROP TRIGGER <trigger name>;

或者，使用：

DROP TRIGGER IF EXISTS <trigger name>;

由于没有触发器，因此不会显示错误消息，因此不会打印警告。

创建示例数据库

为具有以下结构的触发器示例代码创建一个数据库：

1.创建一个名为person的表，其中列有姓名和年龄。

CREATE TABLE person (name varchar(45), age int);

将样本数据插入表中：

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);

选择表格以查看结果：

SELECT * FROM person;

2. 创建一个名为average_age的表，其中有一列名为average：

CREATE TABLE average_age (average double);

将平均年龄值插入表中：

INSERT INTO average_age SELECT AVG(age) FROM person;

选择表格以查看结果：

SELECT * FROM average_age;

3. 创建一个名为person_archive的表，其中包含name、age和time列：

CREATE TABLE person_archive (name varchar(45),age int,time timestamp DEFAULT NOW());

注意：该函数NOW()记录当前时间。从我们的的MySQL日期函数指南和示例中了解有关日期和时间函数的更多信息。

IT外包服务器虚拟化数据存储数据备份数据恢复IT外包服务器虚拟化数据存储数据备份数据恢复

创建一个 BEFORE INSERT 触发器

要创建BEFORE INSERT触发器，请使用：

CREATE TRIGGER <trigger name> BEFORE INSERTON <table name>FOR EACH ROW<trigger body>;

触发器在BEFORE INSERT提交到数据库表之前控制数据修改。BEFORE INSERT将名称大写以保持一致性、检查输入的长度或使用触发器捕获错误输入会在输入新数据之前进一步提供值限制。

BEFORE INSERT 触发器示例

BEFORE INSERT在将数据插入人员表之前创建一个触发器来检查年龄值：

delimiter //CREATE TRIGGER person_bi BEFORE INSERTON personFOR EACH ROWIF NEW.age < 18 THENSIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';END IF; //delimiter ;

插入数据会激活触发器并在提交信息之前检查age的值：

INSERT INTO person VALUES ('John', 14);

控制台显示描述性错误消息。由于触发器检查失败，数据未插入表中。

创建一个 AFTER INSERT 触发器

使用以下命令创建AFTER INSERT触发器：

CREATE TRIGGER <trigger name> AFTER INSERTON <table name>FOR EACH ROW<trigger body>;

当AFTER INSERT输入的行生成更新另一个表所需的值时，触发器很有用。

IT外包服务器虚拟化数据存储数据备份数据恢复IT外包服务器虚拟化数据存储数据备份数据恢复

AFTER INSERT 触发器示例

在person表中插入新行不会自动更新average_age表中的平均值。在person表上创建AFTER INSERT触发器以在插入后更新average_age表：

delimiter //CREATE TRIGGER person_ai AFTER INSERTON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(age) FROM person); //delimiter ;

在person表中插入一个新行会激活触发器：

INSERT INTO person VALUES ('John', 19);

数据成功提交到person表并使用正确的平均值更新average_age表。

创建更新前触发器

使用以下方法BEFORE UPDATE触发：

CREATE TRIGGER <trigger name> BEFORE UPDATEON <table name>FOR EACH ROW<trigger body>;

触发器与触发器BEFORE UPDATE一起使用BEFORE INSERT。如果在插入数据之前存在任何限制，那么在更新之前也应该存在限制。

BEFORE UPDATE 触发器示例

如果在插入数据之前对person表有年龄限制，那么在更新信息之前也应该有年龄限制。没有BEFORE UPDATE触发器，年龄检查触发器很容易避免。没有什么可以将编辑限制为错误的值。

IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦唤鏁版嵁鎭㈠IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦唤鏁版嵁鎭㈠

BEFORE UPDATE将触发器添加到与触发器具有相同主体的personBEFORE INSERT表中：

delimiter //CREATE TRIGGER person_bu BEFORE UPDATEON personFOR EACH ROWIF NEW.age < 18 THENSIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';END IF; //delimiter ;

更新现有值会激活触发器检查：

UPDATE person SET age = 17 WHERE name = 'John';

将年龄更新为小于 18 的值会显示错误消息，并且信息不会更新。

创建 AFTER UPDATE 触发器

使用以下代码块创建AFTER UPDATE触发器：

CREATE TRIGGER <trigger name> AFTER UPDATEON <table name>FOR EACH ROW<trigger body>;

AFTER UPDATE触发器有助于跟踪已提交的数据更改。大多数情况下，插入信息后的任何更改也会在更新数据后发生。

AFTER UPDATE 触发器示例

对person表中年龄数据的任何成功更新也应该更新在average_age表中计算的中间平均值。

在更新person表中的一行后创建一个AFTER UPDATE触发器来更新average_age表：

delimiter //CREATE TRIGGER person_au AFTER UPDATEON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(age) FROM person); //delimiter ;

更新现有数据会更改person表中的值：

UPDATE person SET age = 21 WHERE name = 'John';

更新表person也会更新average_age表中的平均值。

IT外包服务器虚拟化数据存储数据备份数据恢复IT外包服务器虚拟化数据存储数据备份数据恢复

创建一个 BEFORE DELETE 触发器

要创建BEFORE DELETE触发器，请使用：

CREATE TRIGGER <trigger name> BEFORE DELETEON <table name>FOR EACH ROW<trigger body>;

出于安全原因，BEFORE DELETE触发器是必不可少的。如果父表附加了任何子表，则触发器有助于阻止删除并防止孤立表。触发器还允许在删除之前归档数据。

BEFORE DELETE 触发器示例

通过在表person上创建BEFORE DELETE触发器来归档已删除的数据并将值插入person_archive表：

delimiter //CREATE TRIGGER person_bd BEFORE DELETEON personFOR EACH ROWINSERT INTO person_archive (name, age)VALUES (OLD.name, OLD.age); //delimiter ;

从person表中删除数据将数据归档到person_archive表中，然后再删除：

DELETE FROM person WHERE name = 'John';

将值插入回person表中，将删除数据的日志保留在person_archive表中：

INSERT INTO person VALUES ('John', 21);

触发器对于BEFORE DELETE记录任何表更改尝试很有用。

创建一个 AFTER DELETE 触发器

使用以下方法AFTER DELETE触发：

CREATE TRIGGER <trigger name> AFTER DELETEON <table name>FOR EACH ROW<trigger body>;

触发器维护要求数据行在进行更新之前消失的AFTER DELETE信息更新。

AFTER DELETE 触发器示例

在person表上创建AFTER DELETE触发器以使用新信息更新average_age表：

delimiter //CREATE TRIGGER person_ad AFTER DELETEON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //delimiter ;