SQL SERVER 从入门到精通 第5版 第三篇 高级应用 第11章 触发器 读书笔记

 第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