Mysql笔记之触发器

Mysql笔记之触发器

Mysql笔记之存储过程

什么是触发器？

触发器是一种特殊的存储过程，它不是由用户主动发起调用的，而是当发生某一事件而触发，由系统自动调用，比如当用户在数据库中新增一条商品采购记录，我们希望同时在库存中做登记，而库存登记不是人工去录入，是在发生商品采购这一事件时发生，由系统自动完成录入，这个工作就可以交给一个特殊的存储过程来完成，这个存储过程就是触发器。

触发器的工作机制

触发器是建在表上的，当这个表发生新增、修改、删除操作时，如果这个表上有触发器，就会被自动调用，在这个事件的过程中，系统会产生一个临时表， 这个临时表只有一行记录

当执行新增操作时，临时表的名字叫inserted

当执行删除操作时，临时表的名字叫deleted

当执行修改操作时，会同时产生2个临时表，一个是inserted，存放的是新的数据，一个是deleted，存的是旧的数据

当需要触发器连带操作登记库存时就可以从inserted表或者deleted表中获得变量，更新到库存表中数据

触发器的创建

create trigger 触发器名称   --触发器名称
on 表名                 --建在那个表上
for insert|update|delete    --是插入事件处理还是修改事件处理还是删除事件处理
as                       --以下是触发器基本格式
begin
end

参数：

• schema_name

  DML 触发器所属架构的名称。DML 触发器的作用域是为其创建该触发器的表或视图的架构。对于 DDL 或登录触发器，无法指定 schema_name。

• trigger_name

  触发器的名称。trigger_name 必须遵循 标识符规则，但 trigger_name 不能以 # 或 ## 开头。

• table | view

  对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。视图只能被 INSTEAD OF 触发器引用。不能对局部或全局临时表定义 DML 触发器。

• DATABASE

  将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器。

• ALL SERVER

  将 DDL 或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现 event_type 或 event_group，就会激发该触发器。

• WITH ENCRYPTION

  对 CREATE TRIGGER 语句的文本进行模糊处理。使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。

• EXECUTE AS

  指定用于执行该触发器的安全上下文。允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。

• FOR | AFTER

  AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。如果仅指定 FOR 关键字，则 AFTER 为默认值。不能对视图定义 AFTER 触发器。

• INSTEAD OF

  指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。

• { [DELETE] [,] [INSERT] [,] [UPDATE] }

  指定数据修改语句，这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。对于 INSTEAD OF 触发器，不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。同样，也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。

• event_type

  执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。DDL 事件中列出了 DDL 触发器的有效事件。

• event_group

  预定义的 Transact-SQL 语言事件分组的名称。执行任何属于 event_group 的 Transact-SQL 语言事件之后，都将激发 DDL 触发器。DDL 事件组中列出了 DDL 触发器的有效事件组。CREATE TRIGGER 运行完毕之后，event_group 还可通过将其涵盖的事件类型添加到 sys.trigger_events 目录视图中来作为宏使用。

• WITH APPEND

  指定应该再添加一个现有类型的触发器。仅当兼容级别等于或低于 65 时，才需要使用此可选子句。如果兼容级别等于或高于 70，则不需要使用 WITH APPEND 子句来添加现有类型的其他触发器。这是兼容级别设置等于或高于 70 的 CREATE TRIGGER 的默认行为。WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器，则也不能使用该子句。仅当为了向后兼容而指定了 FOR 时（但没有 INSTEAD OF 或 AFTER）时，才能使用 WITH APPEND。如果指定了 EXTERNAL NAME（即触发器为 CLR 触发器），则不能指定 WITH APPEND。

• NOT FOR REPLICATION

  指示当复制代理修改涉及到触发器的表时，不应执行触发器。有关详细信息，请参阅使用 NOT FOR REPLICATION 来控制约束、标识和触发器。

• sql_statement

  触发条件和操作。触发器条件指定其他标准，用于确定尝试的 DML、DDL 或 logon 事件是否导致执行触发器操作。尝试上述操作时，将执行 Transact-SQL 语句中指定的触发器操作。触发器可以包含任意数量和种类的 Transact-SQL 语句，但也有例外。触发器的用途是根据数据修改或定义语句来检查或更改数据；它不应向用户返回数据。触发器中的 Transact-SQL 语句常常包含控制流语言。DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，则使用：SELECT * FROM deleted DDL 和登录触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息。DELETE、INSERT 或 UPDATE 触发器中，如果兼容级别设置为 70，则 SQL Server 不允许在 inserted 和 deleted 表中存在 text、ntext 或 image 列引用。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。如果兼容级别等于或低于 65，则将为 inserted 或 deleted 的文本、ntext 或者允许 Null 值的 image 列返回 Null 值；如果列不能为 Null，则返回零长度的字符串。如果兼容级别等于或高于 80，SQL Server 将允许通过表或视图的 INSTEAD OF 触发器来更新 text、ntext 或 image 列。

• < method_specifier>

  对于 CLR 触发器，指定程序集与触发器绑定的方法。该方法不能带有任何参数，并且必须返回空值。class_name 必须是有效的 SQL Server 标识符，并且该类必须存在于可见程序集中。如果该类有一个使用“.”来分隔命名空间部分的命名空间限定名称，则类名必须用 [] 或“ ”分隔符分隔。该类不能为嵌套类。

参考文章：

https://blog.csdn.net/DipsyAndPo/article/details/79023938