网站统计系统,石岩附近做网站公司,教育行业网站制作,视频网站开发防止盗链文章目录 1.简介2.行级与语句级触发器3.触发时机4.触发器优缺点5.创建触发器语法示例 6.查看触发器7.删除触发器参考文献 1.简介
触发器#xff08;Trigger#xff09;是与表关联的命名数据库对象#xff0c;当表发生特定事件时激活。 触发器的一些用途是对要插入表中的值执… 文章目录 1.简介2.行级与语句级触发器3.触发时机4.触发器优缺点5.创建触发器语法示例 6.查看触发器7.删除触发器参考文献 1.简介
触发器Trigger是与表关联的命名数据库对象当表发生特定事件时激活。 触发器的一些用途是对要插入表中的值执行检查或对更新中涉及的值执行计算。
触发器也是一种存储程序它和一个指定的表相关联当该表中的数据发生变化增加、更新、删除时自动执行。 这些修改数据行的操作被称为触发器事件例如 INSERT 或者 LOAD DATA 等插入数据的语句可以激活插入触发器。触发器可以设置为在触发事件之前或之后激活。 例如您可以在插入表中的每行之前或更新每行之后激活触发器。
通过编写触发器你可以实现数据的验证、记录变更、数据同步等操作。触发器是数据库中强大的功能之一可以在保持数据完整性和一致性方面发挥重要作用。
2.行级与语句级触发器
按照 SQL 标准触发器可以分为行级触发器row-level trigger和语句级触发器 statement-level trigger。行级触发器对于修改的每一行数据都会激活一次如果一个语句插入了 100 行数据将会调用触发器 100 次语句级触发器针对每个语句激活一次一个插入 100 行数据的语句只会调用一次触发器。
MySQL 只支持行级触发器不支持预语句级触发器。
3.触发时机
在 MySQL 中只有执行增加、更新和删除操作时才能触发触发器的执行。
不同事件可以激活不同类型的触发器。
INSERT 事件触发器用于插入数据的操作包括 INSERT、LOAD DATA、REPLACE 语句等。UPDATE 事件触发器用于更新操作例如 UPDATE 语句。DELETE 事件触发器用于删除操作例如 DELETE 和 REPLACE 语句等。
DROP TABLE 和 TRUNCATE TABLE 语句不会激活删除触发器。
另外MySQL 触发器可以在触发事件之前或者之后执行分别称为 BEFORE 触发器和 AFTER 触发器。这两种触发时机可以和不同的触发事件进行组合例如 BEFORE INSERT 触发器或者 AFTER UPDATE 触发器。
4.触发器优缺点
MySQL 触发器的优点包括
记录并审核用户对表中数据的修改操作实现审计功能。实现比检查约束更复杂的完整性约束例如禁止非业务时间的数据操作。实现某种业务逻辑例如增加或删除员工时自动更新部门中的人数。实时同步表中的数据。
虽然触发器功能强大但是它也存在一些缺点
触发器会增加数据库结构的复杂度而且触发器对应用程序不可见难以调试。触发器需要占用更多的数据库服务器资源尽量使用数据库提供的非空、唯一、检查约束等。触发器不能接收参数只能基于当前的触发对象进行操作。
针对特殊场景使用触发器可以带来一定的便利性但不要过渡依赖触发器避免造成数据库的性能下降和维护困难。接下来我们介绍触发器的管理操作。
5.创建触发器
语法
MySQL 使用CREATE TRIGGRT语句创建触发器基本语法如下
CREATE[DEFINER user]TRIGGER [IF NOT EXISTS] trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROW[trigger_order]trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }trigger_order: { FOLLOWS | PRECEDES } other_trigger_nameDEFINER 子句是可选的用于指定触发器的创建者或所有者。它定义了谁有权利执行和管理这个触发器。具体来说DEFINER 子句决定了触发器在哪个数据库用户的权限下运行。如果存在 DEFINER 子句则用户值应为指定为 ‘user_name’‘host_name’、CURRENT_USER 或 CURRENT_USER() 的 MySQL 帐户允许的用户值取决于您拥有的权限。缺省为当前执行创建操作的用户。
trigger_name 是触发器的名称。
trigger_time 指定触发器的触发时机在触发事件之前BEFORE 还是之后AFTER执行。
trigger_event 用于定义触发事件的类型 INSERT、UPDATE 还是 DELETE。
table_name 是触发器关联的表名不能是临时表或者视图。
FOR EACH ROW 表明这是一个行级触发器。
trigger_order 是可选的用于指定与其他具有相同触发事件和动作时间的现有触发器的先后触发关系。如果没有指定任何选项默认情况下按照触发器的创建顺序执行。
trigger_body 是触发器执行的具体语句。
在触发器主体中您可以通过使用别名 OLD 和 NEW 来引用主题表与触发器关联的表中的列。OLD.col_name 引用更新或删除之前的现有行的列。NEW.col_name 是指要插入的新行或更新后的现有行的列。
示例
下面是一个简单的示例它将触发器与表关联起来以激活 INSERT 操作。 触发器充当累加器对插入表的某一列中的值求和。
mysql CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)mysql CREATE TRIGGER ins_sum BEFORE INSERT ON accountFOR EACH ROW SET sum sum NEW.amount;
Query OK, 0 rows affected (0.01 sec)上面创建了一个名为 ins_sum 的触发器该触发器与 ins_sum 表关联。 它还指定了触发器动作时间BEFORE、触发事件INSERT以及触发器激活时执行的操作的子句。
要使用触发器请将累加器变量设置为零执行 INSERT 语句然后查看变量的值。
mysql SET sum 0;
mysql INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql SELECT sum AS Total amount inserted;
-----------------------
| Total amount inserted |
-----------------------
| 1852.48 |
-----------------------6.查看触发器
使用 SHOW TRIGGERS 语句可以查看数据库中的触发器列表。
SHOW TRIGGERS[{FROM | IN} db_name][LIKE pattern | WHERE expr]db_name 用于查看指定数据库中的触发器默认为当前数据库。
LIKE 子句如果存在指示要匹配的表名称不是触发器名称显示这些表的触发器。
可以使用 WHERE 子句来使用更通用的条件筛选。
比如查看上面创建的触发器 ins_sum。
mysql SHOW TRIGGERS LIKE acc%\G
*************************** 1. row ***************************Trigger: ins_sumEvent: INSERTTable: accountStatement: SET sum sum NEW.amountTiming: BEFORECreated: 2023-08-24 10:10:12.61sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner: melocalhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci另外MySQL 系统表 INFORMATION_SCHEMA.TRIGGERS 中包含了更详细的触发器信息。
如果想要获取创建某个触发器的 DDL 语句可以 SHOW CREATE TRIGGER 语句。
mysql SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************Trigger: ins_sumsql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINERmelocalhost TRIGGER ins_sumBEFORE INSERT ON accountFOR EACH ROW SET sum sum NEW.amountcharacter_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ciCreated: 2023-08-24 10:10:12.617.删除触发器
MySQL 没有提供修改触发器的语句只能通过 DROP TRIGGER 语句删除并再次创建触发器。
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name该语句会删除一个触发器。 模式数据库名称是可选的。 如果省略模式则会在当前的数据库中查找并删除触发器。DROP TRIGGER 需要与触发器关联的表的 TRIGGER 权限。
IF EXISTS 可以避免触发器 tri_audit_salary 不存在时产生错误。
例如以下语句可以用于删除触发器 ins_sum。
DROP TRIGGER IF EXISTS ins_sum;参考文献
MySQL 8.0 Reference Manual :: 25 Stored Objects MySQL 8.0 Reference Manual :: 25.3 Using Triggers 13.1.22 CREATE TRIGGER Statement 13.7.7.40 SHOW TRIGGERS Statement 13.7.7.11 SHOW CREATE TRIGGER Statement 13.1.34 DROP TRIGGER Statement 《MySQL 入门教程》第 33 篇 触发器
