教你如何使用MySQL触发器
MySQL 中的列应用了少量的值限制。例如，将列数据类型设置为tiny int而不是 null需要一个小的数值输入。尽管如此，仍需要更多的限制来
|
MySQL 触发器在添加、更新或删除表行时对表应用限制。 MySQL 中的列应用了少量的值限制。例如，将列数据类型设置为tiny int而不是 null需要一个小的数值输入。尽管如此，仍需要更多的限制来保持数据的完整性。 本教程向您展示如何使用 MySQL 触发器并为每种类型的触发器提供示例。
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 先决条件 MySQL 中的触发器是什么？ 触发器是一个命名的 MySQL 对象，它在表中发生事件时激活。触发器是与特定表关联的特定类型的存储过程。 NEW触发器允许使用和访问表中的值以进行比较OLD。修饰符的可用性取决于您使用的触发事件：
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 在尝试插入数据时检查或修改值使NEW.修饰符可用。这是因为表格已更新为新内容。相反，OLD.插入语句的值不存在，因为事先没有信息存在于其位置。 更新表格行时，两个修饰符都可用。有OLD.我们想更新为数据的NEW.数据。 最后，当移除一行数据时，OLD.修饰符访问移除的值。NEW.不存在，因为删除时没有任何东西可以替换旧值。 注意：通过学习如何使用某些命令来重命名 MySQL 中的列，可以轻松管 理数据库。 MySQL 触发器示例 作为应用触发器的示例，将新值插入到person表中会产生与原始输入不同的结果：
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 请注意，插入的名称最初是小写的。选择表格时，第一个字母显示为大写。尽管没有任何与常规插入语句不同的迹象，但触发器在插入语句之前触发以大写名称的第一个字母。 使用 MySQL 触发器 与表关联的每个触发器都具有基于两个因素的唯一名称和功能： 1.时间。BEFORE或AFTER特定的行事件。 2.事件。INSERT,UPDATE或DELETE.
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 MySQL 触发器根据激活时间和事件触发，总共有六个独特的触发器组合。before 语句有助于在提交之前检查数据并进行更改，而 after 语句首先提交数据然后执行语句。 一组动作的执行自动发生，影响语句中所有插入、删除或更新的行。 创建触发器 使用CREATE TRIGGER语句语法创建新触发器： CREATE TRIGGER ON FOR EACH ROW; 最佳做法是使用以下信息命名触发器： __ 例如，如果触发器在名为employee的表上插入之前触发，则最好的约定是调用触发器： before_employee_insert 或者，一种常见的做法是使用以下格式： _ 表employee的前插入触发器名称如下所示： employee_bi 对于受函数影响的每一行mysql触发器，触发器在由定义的表上发生事件的特定时间执行。 IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦唤 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦唤 鏁版嵁鎭㈠ 删除触发器 要删除触发器，请使用以下DROP TRIGGER语句： DROP TRIGGER ; IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 或者，使用： DROP TRIGGER IF EXISTS ; IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 由于没有触发器，因此不会显示错误消息，因此不会打印警告。 创建示例数据库 为具有以下结构的触发器示例代码创建一个数据库： 1.创建一个名为person的表，其中列有姓名和年龄。 CREATE TABLE person (name varchar(45), age int); 将样本数据插入表中： INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20); 选择表格以查看结果： SELECT * FROM person;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 2. 创建一个名为average_age的表，其中有一列名为average： CREATE TABLE average_age (average double); 将平均年龄值插入表中： INSERT INTO average_age SELECT AVG(age) FROM person; 选择表格以查看结果： SELECT * FROM average_age;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 3. 创建一个名为person_archive的表，其中包含name、age和time列： CREATE TABLE person_archive (name varchar(45),age int,time timestamp DEFAULT NOW()); IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 注意：该函数NOW()记录当前时间。从我们的的MySQL日期函数指南和示例中了解有关日期和时间函数的更多信息。 IT外包 服务器虚拟化 数据存储 数据备份 数据恢复IT外包 服务器虚拟化 数据存储 数据备份 数据恢复 创建一个 BEFORE INSERT 触发器 要创建BEFORE INSERT触发器，请使用： CREATE TRIGGER BEFORE INSERTON FOR EACH ROW; 触发器在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 ;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 插入数据会激活触发器并在提交信息之前检查age的值： INSERT INTO person VALUES ('John', 14); IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 控制台显示描述性错误消息。由于触发器检查失败，数据未插入表中。 创建一个 AFTER INSERT 触发器 使用以下命令创建AFTER INSERT触发器： CREATE TRIGGER AFTER INSERTON FOR EACH ROW; 当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 ;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 在person表中插入一个新行会激活触发器： INSERT INTO person VALUES ('John', 19);
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 数据成功提交到person表并使用正确的平均值更新average_age表。 创建更新前触发器 使用以下方法BEFORE UPDATE触发： CREATE TRIGGER BEFORE UPDATEON FOR EACH ROW; 触发器与触发器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 ;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 更新现有值会激活触发器检查： UPDATE person SET age = 17 WHERE name = 'John'; IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 将年龄更新为小于 18 的值会显示错误消息，并且信息不会更新。 创建 AFTER UPDATE 触发器 使用以下代码块创建AFTER UPDATE触发器： CREATE TRIGGER AFTER UPDATEON FOR EACH ROW; 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 ;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 更新现有数据会更改person表中的值： UPDATE person SET age = 21 WHERE name = 'John';
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 更新表person也会更新average_age表中的平均值。 IT外包 服务器虚拟化 数据存储 数据备份 数据恢复IT外包 服务器虚拟化 数据存储 数据备份 数据恢复 创建一个 BEFORE DELETE 触发器 要创建BEFORE DELETE触发器，请使用： CREATE TRIGGER BEFORE DELETEON FOR EACH ROW; 出于安全原因，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 ;
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 从person表中删除数据将数据归档到person_archive表中，然后再删除： DELETE FROM person WHERE name = 'John';
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 将值插入回person表中，将删除数据的日志保留在person_archive表中： INSERT INTO person VALUES ('John', 21);
IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 触发器对于BEFORE DELETE记录任何表更改尝试很有用。 创建一个 AFTER DELETE 触发器 使用以下方法AFTER DELETE触发： CREATE TRIGGER AFTER DELETEON FOR EACH ROW; 触发器维护要求数据行在进行更新之前消失的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 ; IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 从person表中删除一条记录会使用新的平均值更新average_age表： IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 没有AFTER DELETE触发器，信息不会自动更新。 创建多个触发器 MySQL 不支持同时触发多个触发器。但是，可以向同一个触发器添加多个逻辑操作。使用BEGIN和END定界符来指示触发器主体： CREATE TRIGGER ON FOR EACH ROWBEGIN;END; 确保在创建具有多个操作的触发器之前更改默认分隔符。 显示触发器 列出数据库中的所有触发器： SHOW triggers; 输出显示所有触发器的列表，包括名称和语句内容： IT外包|服务器虚拟化|数据存储|数据备份|网络故障维修|数据恢复|异地组网 还会显示其他信息，例如创建时间和创建触发器的用户。 结论 MySQL 触发器在特定事件发生之前或之后提供对数据的进一步验证和控制。无论您是试图防止错误还是添加一致性限制，触发器都有助于控制数据输入、更新和删除。 请记住，触发器检查是按行进行的，这会导致性能因大量查询而降低。 IT外包 服务器虚拟化 数据存储 数据备份 数据恢复IT外包 服务器虚拟化 数据存储 数据备份 数据恢复 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
