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

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

时间:2024-04-29 21:22:06浏览次数:20  
标签:11 触发器 读书笔记 -- 创建 数据库 视图 SERVER DDL

 第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

相关文章

  • springboot连接sqlserver2008r2 驱动版本问题。
    <dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>8.2.2.jre8</version></dependency>版本比较常见的:8.4.1.jre8最新的提示:9......
  • ZCMU-1111
    与背包和动态规划有关(我认为)采用dp数组存放吃掉i千克食物要用掉的钱dp最开始要尽量的大方便过程中判断和最后的输出判断实时更新dp,保留最小的钱以前不知道的printf函数可以这样用fill函数填充数组,(开始,结束,填充值);C和C++结构体里面可以放函数学习#include<c......
  • ZCMU-1129
    数学公式题罢了学长1.斯特灵公式:2.对数公式(因为以10为底,得到的是10^x,所以最后向下取整加上1);#include<cstdio>#include<cmath>usingnamespacestd;constdoublePI=acos(-1);constdoublee=exp(double(1));intstr(intn){returnfloor(log10(sqrt(2*PI*n))+......
  • p1182
    #include<stdio.h>#include<stdlib.h>#include<math.h>#include<string.h>#defineN100intF(intn,inta[N]){inti,j,p,q,m,t;intb[N]={0};if(n==0){a[0]=7;}elseif(n==1){a[0]=1;a[......
  • p1140 C语言循环数
    #include<stdio.h>#include<stdlib.h>#include<math.h>#include<string.h>#defineN100intmain(){intn,x,y,i,j,p,q,t,s;chara[N]={'0'};while(scanf("%s",&a)!=EOF){intb[N];......
  • java 数据库编程(一)JDBC连接Sql Server数据库
    java数据库编程(一)JDBC连接SqlServer数据库一、JDBC简介java数据库连接技术(JavaDatabaseConnection,JDBC)是由java提供的一组与平台无关的数据库的操作标准,其本身由一类与接口组成,并且在操作中将按照严格的顺序执行。由于数据库属于资源操作,所以所有的数据库操作的最后必......
  • Mysql、Oracle、SqlServer的JDBC连接实现和对比(提供驱动包)
    Mysql、Oracle、SqlServer的JDBC连接实现和对比(提供驱动包)首先,我们需要准备数据库连接所需的jar包。目前mysql的驱动包可能比较好找,但是oracle和sqlserver的有很多,要找到能用的要花一点点心思,这里直接把下载地址和版本发送出来。Mysql:驱动程序包:mysql-connector-java-5.1.3......
  • 代码11
    """askcolor颜色选择框测试,改变背景色"""fromtkinterimport*fromtkinter.colorchooserimport*#创建主窗口root=Tk()root.geometry("400x150")#设置窗口大小#定义函数,用于调用颜色选择框并改变背景色deffunc():#调用颜色选择框,初始颜色为红色,对话框标......
  • winscp/putty 免密登录linux server
    参考 puttyWinScp免密登录远程Linux 原理生成一对公钥和私钥,私钥以文件的形式保存在本地,公钥保存在远程机器上,一般是/home/users/username/.ssh/authorized_keys。这样每次登录只需指定私钥文件,远程机器通过比对公钥和私钥来验证登录的合法性第一步:生成公钥/私钥双击putt......
  • 30秒出服装设计稿,森马用Serverless+AIGC 整“新活”!
    “创新项目如何去赋能我们的业务,这件事情在森马很重要。阿里云函数计算帮我们屏蔽掉了想把AI落地到实际业务场景中 GPU 算力资源储备、采购成本、技术门槛等很多难题,从而迅速做出决策,快人一步站在正确的起点,体验新技术对整个服装爆款设计、营销链路带来的改变。”—— 林建霞......