第11章 触发器
>.概述
触发器是一种特殊类型的存储过程.当指定表中的数据发生变化时触发器自动生效.它与表紧密相连,可以看作表定义的一部分.触发器不能通过名称被直接调用,更不允许设置参数.
在SQL SERVER 中,一张表可以有多个触发器.用户可以使用INSERT,UPDATE或者DELETE语句对触发器进行设置,也可以对一张表上的特定操作,设置多个触发器.触发器可以包含复杂的SQL语句,不论触发器进行的操作有多复杂,它都只作为一个独立的单元被执行,被看作一个事务.如果在执行触发器的过程中发生了错误,则整个事务将会自动回滚.
>.触发器的优点.
>.自动执行.
>.可以调用一个或者多个存储过程,甚至是数据库之外的外部过程.
>.能够实现比CHECK约束更为复杂的数据完整性约束.CHECK约束不允许引用其他表中的列来完成检查工作,但触发器可以.
>.可以检测数据库内的操作,从而取消数据库未经许可的更新操作,使修改与更新更加安全与稳定
>.能够对数据库中的表进行级联更改.
>.一个表中可以同时存在3种不同类型的触发器(INSERT,UPDATE,DELETE)
>.触发器的类型
>.AFTER触发器:在执行了INSERT,UPDATE或者DELETE语句操作之后执行AFTER触发器.
>.INSTEAD OF触发器:它是一种特殊类型的数据库触发器,它可以在触发条件满足时代替通常的触发动作。通常的触发动作可能包括插入、更新或删除操作。INSTEAD OF触发器允许你在执行这些操作时定义自定义逻辑,而不是数据库默认的操作。此外,你还可以为一个或多个基表的视图定义INSTEAD OF触发器。这些触发器能够在视图上支持更新操作,扩展视图的功能。这意味着你可以通过视图进行更新操作,并且通过定义适当的INSTEAD OF触发器来处理这些更新,从而实现自定义的行为。
取个例子:
假设你有一个数据库中的视图,名为 SalesSummary,它聚合了销售数据。这个视图可能包含了来自多个表的数据,并提供了一个简化的方式来查看销售总额、平均销售额等信息。
现在,你想要允许用户通过这个视图执行更新操作,例如插入新的销售数据、更新现有销售记录或删除不需要的销售数据。但是,由于视图是基于多个表的聚合,数据库系统无法直接确定如何在这些表上执行这些更新操作。
为了解决这个问题,你可以创建一个INSTEAD OF触发器,它会在用户尝试执行更新操作时代替数据库系统默认的操作。让我们看看如何创建一个简单的INSTEAD OF触发器,以处理对 SalesSummary 视图的更新操作:
-- 创建一个INSTEAD OF触发器来处理对SalesSummary视图的更新操作 CREATE TRIGGER InsteadOfSalesSummaryUpdate ON SalesSummary INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN -- 这里是你自定义的逻辑,你可以根据需要执行任何操作 -- 示例:在插入新的销售数据之前,你可能想要对数据进行验证 IF EXISTS (SELECT 1 FROM inserted WHERE SalesAmount < 0) BEGIN RAISERROR ('Sales amount cannot be negative.', 16, 1); ROLLBACK TRANSACTION; RETURN; END -- 示例:在更新销售记录之前,你可能想要记录下谁做了这个更新 INSERT INTO SalesAuditLog (SalesID, Action, UpdatedBy, UpdatedDateTime) SELECT SalesID, 'Update', SYSTEM_USER, GETDATE() FROM inserted; -- 示例:在删除销售数据之前,你可能需要在其他表中进行相关的清理操作 DELETE FROM SalesDetails WHERE SalesID IN (SELECT SalesID FROM deleted); -- 最后,你可能需要实际执行对基表的更新操作 -- 例如,对于SalesSummary视图,你可能需要更新多个基表中的相应数据 -- 但这个更新过程将根据你的数据库架构而不同 -- 提交事务,表示更新操作已成功完成 COMMIT TRANSACTION; END;
在这个例子中,INSTEAD OF触发器 InsteadOfSalesSummaryUpdate 代替了对 SalesSummary 视图的插入、更新和删除操作。你可以根据实际情况扩展这个触发器,以满足你的特定需求。
>.CLR触发器:CLR触发器可以是AFTER触发器或者INSTEAD OF触发器,还可以是DDL触发器.CLR触发器将执行在托管代码(.NET Framework)中编写的方法,而不用执行SQL存储过程.
>.DDL触发器:DDL触发器是一种数据库触发器,它会在数据库的结构或模式发生变化时触发。DDL(Data Definition Language)指的是用于定义数据库结构的语言,比如创建、修改或删除表、索引等操作。
与普通的DML(Data Manipulation Language)触发器不同,DDL触发器会在对数据库的结构进行更改时被触发,而不是在对数据进行增删改查时触发。这意味着当执行诸如创建表、删除列等操作时,DDL触发器可以执行特定的逻辑。
举个例子,假设你想要在数据库中创建一个DDL触发器,以便在有新表被创建时记录下创建时间和创建者的信息。以下是一个简单的DDL触发器示例:
-- 创建一个DDL触发器来记录表的创建信息 CREATE TRIGGER DDL_TableCreated ON DATABASE FOR CREATE_TABLE AS BEGIN DECLARE @TableName NVARCHAR(128); DECLARE @CreatedBy NVARCHAR(128); DECLARE @CreatedDateTime DATETIME; SET @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'); SET @CreatedBy = SYSTEM_USER; SET @CreatedDateTime = GETDATE(); -- 在这里你可以将表的创建信息插入到日志表中,或者进行其他自定义的操作 INSERT INTO TableCreationLog (TableName, CreatedBy, CreatedDateTime) VALUES (@TableName, @CreatedBy, @CreatedDateTime); END;
在这个例子中,DDL触发器 DDL_TableCreated 会在数据库中创建新表时触发。当触发器被激活时,它会获取新创建的表的名称、创建者和创建时间,并将这些信息记录到日志表中。这样,你就可以跟踪数据库中表的创建历史。
>.创建触发器
这里要需要说明一下,对 [表] 创建的触发器,会保存在 [表] 下面的触发器里,不是 [可编程性] - [数据库触发器] 下面.如图:
写完创建过程后,直接点菜单上的 [执行(x)] 即可保存
警告:触发器也是可以 触发 触发器的!!!这也是为什么大厂不让用触发器的原因
>.创建DML触发器.如果用户通过DML事件编辑数据,则执行DML触发器.DML事件是针对表或视图的INSERT,UPDATE或者DELETE语句.
-- 创建一个DML触发器,在对指定表进行插入操作时触发 CREATE TRIGGER MyDMLTrigger ON MyTable AFTER INSERT AS BEGIN -- 在这里添加你的逻辑 -- 例如,记录插入的数据或执行其他操作 PRINT 'A new row has been inserted into MyTable.'; END;
>.创建DDL触发器.用于响应各种DDL事件.这结事件主要对应于CREATE,ALTER和DROP语句,以及执行类似DDL操作的某些系统存储过程.
-- 创建一个DDL触发器,在对数据库中创建表时触发 CREATE TRIGGER MyDDLTrigger ON DATABASE FOR CREATE_TABLE AS BEGIN -- 在这里添加你的逻辑 -- 例如,记录表的创建信息或执行其他操作 PRINT 'A new table has been created in the database.'; END;
>.创建登录触发器.在遇到 LOGON事件时触发 .LOGON事件是在建立用户会话时触发的.
-- 创建拒绝用户名为'MR'的用户登录的触发器 CREATE TRIGGER trg_Prevent_MR_Login ON ALL SERVER FOR LOGON AS BEGIN DECLARE @UserName NVARCHAR(128); SET @UserName = ORIGINAL_LOGIN(); IF @UserName = 'MR' BEGIN ROLLBACK; END END;
>.管理触发器
一.查看所有触发器
-- 该查询语句用于获取类型为'TR'(即触发器)的对象的名称和定义信息 SELECT name, -- 触发器对象的名称 definition -- 触发器对象的定义信息 FROM sys.sql_modules AS m INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id -- 通过INNER JOIN将sys.sql_modules和sys.all_objects连接在一起,条件是object_id匹配 WHERE o.[type] = 'TR'; -- 筛选出类型为'TR'的对象(即触发器),可查看以下官网链接,筛选对应对象之类型
利用上面的语句,能把当前数据库中所有的触发器展示出来:
下图来源于:sys.sysobjects (Transact-SQL) - SQL Server | Microsoft Learn
书上还提供了另外一种方法来查看所有触发器,但是感觉没上面这种给力:
SELECT * FROM sys.objects WHERE type='tr'
二.使用sp_helptext存储过程来查看指定触发器
EXEC sys.sp_helptext 'aaa_ins'
>.修改触发器
创建触发器我们用 CREATE TRIGGER 关键字,修改触发器我们用 ALTER TRIGGER关键字来修改即可.
>.重命名触发器
EXEC sp_rename 'aaa_ins', 'bbb_ins';
>.禁用与启用触发器
被禁用的触发器,并不会被删除掉,而右下角显示一个小红x,以区别启用状态的触发器.可以通过SQL语句设置,也可以通过右键菜单设置
-- 禁用触发器,aaa是一个表名,aaa_ins是触发器 DISABLE TRIGGER aaa_ins ON aaa; -- 启用触发器 ENABLE TRIGGER aaa_ins ON aaa;
>.删除触发器
用DROP TRIGGER关键字删除,也可以通过右键菜单删除 ,aaa是一个表名,aaa_ins是触发器
DROP TRIGGER aaa_ins ON aaa
标签:11,触发器,读书笔记,--,创建,数据库,视图,SERVER,DDL From: https://www.cnblogs.com/yoooos/p/18131985