SQL Server之DML触发器
一、如何创建一个触发器呢
触发器的定义语言如下:
CREATE [ OR ALTER ] TRIGGER trigger_nameon {table_name | view_name}{for | After | Instead of }[ insert, update,delete ]assql_statement
从这个定义语言我们可以知道如下信息:
- trigger_name:触发器的名称。 trigger_name 必须遵循标识符规则,但 trigger_name 不得以 # 或 ## 开头。
- table | view:触发器可以作用于表或视图,只有 INSTEAD OF 触发器才能引用视图
- FOR | AFTER:FOR 或 AFTER 指定仅当触发 SQL 语句中指定的所有操作都已成功启动时,DML 触发器才触发。
- INSTEAD OF:指定 DML 触发器(而不是触发 SQL 语句)启动,因此替代触发语句的操作。
从定义中我们可以发现:DML触发器总体有两种类型:AFTER 触发器和INSTEAD OF 触发器
二、AFTER 触发器和INSTEAD OF 触发器的区别
直接区别它们可能晦涩难懂,我们先举个例子
(1)建一张表
--作家表
CREATE TABLE my_test.dbo.author (id bigint IDENTITY(0,1) NOT NULL,name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
(2)在author创建after触发器
CREATE TRIGGER authorTrigger ON author AFTER INSERT AS
declare @count int
BEGINselect @count = count(*) from authorprint @count
END
该触发器作用就是统计insert后的表数量,测试结果如下:
我们插入前数据条数

执行insert语句后,触发触发器,输出的条数是


(3)修改author的触发器为INSTEAD OF
alter TRIGGER authorTrigger ON author INSTEAD OF INSERT AS
declare @count int
BEGINselect @count = count(*) from authorprint @count
END
该触发器作用就是统计insert后的表数量,测试结果如下:
我们插入前数据条数

执行insert语句后,触发触发器,输出的条数是


看到这里,读者可能怀疑数据错了,第二种触发器INSTEAD OF为何插入后还是七条呢,不应该是八条吗
根据这个实验引出结论,
- after 触发器(insert、update、delete触发器)内的语句是在操作执行之后(已经作用在表上)才触发执行的
- instead of 触发器并不会执行操作(不会影响实际的表),它更像一个指令,遇到条件中的指令就触发了,就会执行触发器内的语句。
- 在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器
- INSTEAD OF 触发器可用于对一个或多个列执行错误或值检查,然后在插入、更新或删除行之前执行其他操作
无论after 触发器还是instead of 触发器,他们的作用都是在更新或更新后对表中数据操作,那么更新的旧数据该保存到哪里呢,sqlserver提供了两张临时表inserted 和 deleted 表
三、inserted 和 deleted 表
inserted表和deleted表对照
修改操作记录 inserted表 deleted表 增加(insert)记录 存放新增的记录 ............ 删除(deleted)记录 .............. 存放被删除的记录 修改(update)记录 存放更新后的记录 存放更新前的记录
- inserted表保存更新后的记录副本,deleted表保存更新前的记录副本。
- INSTEAD OF触发器和AFTER 触发器都可以使用inserted表和deleted表
- SQL Server 会自动创建和管理这两种表,用户使用它们作为条件,但是不能修改表中的数据
四、项目实战
开发中,有这么一个需求:当表中某些字段发生修改或者新增一条记录时,会自动更新表中modifyTime字段为当前时间,如何采用触发器实现呢?
分析:这里要注意是某些字段有更新,才更新modifyTime字段;如果你更新的字段不在指定的字段里,是不会更新modifyTime字段,答案如下:
CREATE TRIGGER modifMeterTrigger ON
Meter after UPDATE,INSERT AS
declare @upflag int
BEGINselect@upflag = casewhen d.IsAddSecurityPlan != i.IsAddSecurityPlan then 1when d.MeterVersion != i.MeterVersion then 1when d.SystemNo != i.SystemNo then 1when d.IsCancel != i.IsCancel then 1when d.UseDate != i.UseDate then 1when d.HankDate != i.HankDate then 1when d.UseYear != i.UseYear then 1when d.AddressCode != i.AddressCode then 1when d.MeterType != i.MeterType then 1when d.UserGasType != i.UserGasType then 1else 0endfrom inserted i left join deleted d on d.id = i.idif(@upflag > 0)UPDATE Meter SET ModifTime = GETDATE( )FROM Meter t INNER JOIN Inserted i ON t.id=i.id
END
看这个触发器定义语句,比之前的要复杂好多,我慢慢解读
(1)首先定义的After类型的触发器
(2)定义了一个局部变量,@upflag来标志更新的字段是否在指定的字段内
(3)将inserted和deleted采用左连接,通过case when来判断,如果inserted字段的值不等于deleted中的值,说明指定字段有更新,然后更新标志为@upflag=1
(4)最后判断@upflag是否为1,为1执行更新
(5)采用原表和Inserted内连接主要为了既可以批量更新也可以单条更新
注意一点:我们在更新可能会影响多行数据,如果我们在更新时采用id作为条件
比如如下触发器定义(来源于官方):
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = SubTotal + LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;
对于多行插入,示例的 DML 触发器可能不会正确运行,SQL官方提供了@@ROWCOUNT 函数来区分单行插入和多行插入
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal = SubTotal + LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted)
END; 相关文章:
SQL Server之DML触发器
一、如何创建一个触发器呢 触发器的定义语言如下: CREATE [ OR ALTER ] TRIGGER trigger_nameon {table_name | view_name}{for | After | Instead of }[ insert, update,delete ]assql_statement从这个定义语言我们可以知道如下信息: trigger_name&…...
04. 【Linux教程】安装 Linux 操作系统
通过前面的小节学习,我们已经对 Linux 操作系统有了简单的了解,同时也在 Windows 下安装了虚拟机软件 VMware ,那么本节课我们就介绍下如何使用虚拟机软件安装 Linux 操作系统。 通过第一小节的学习我们知道 Linux 有很多的发行版本…...
Facebook群控:利用IP代理提高聊单效率
在当今社交媒体竞争激烈的环境中,Facebook已经成为广告营销和推广的重要平台,为了更好地利用Facebook进行推广活动,群控技术应运而生。 本文将深入探讨Facebook群控的定义、作用以及如何利用IP代理来提升群控效率,为你提供全面的…...
香港倾斜模型3DTiles数据漫游
谷歌地球全香港地区倾斜摄影数据,通过工具转换成3DTiles格式,将这份数据完美加载到三维数字地球Cesium上进行完美呈现,打造香港地区三维倾斜数据覆盖,完美呈现香港城市壮美以及维多利亚港繁荣景象。再由12.5米高分辨率地形数据&am…...
Go指针探秘:深入理解内存与安全性
目录 1. 指针的基础1.1 什么是指针?1.2 内存地址与值的地址1.2.1 内存中的数据存储1.2.2 如何理解值的地址 2. Go中的指针操作2.1 指针类型和值2.1.1 基本数据类型的指针2.1.2 复合数据类型的指针 2.2 如何获取一个指针值2.3 指针(地址)解引用…...
Oracle12c之Sqlplus命令行窗口基本使用
Oracle12c之Sqlplus命令行窗口基本使用 文章目录 Oracle12c之Sqlplus命令行窗口基本使用1. 连接1. 超级用户2. 普通用户1. 创建普通用2. 连接 2. 修改用户连接数1. 查看默认连接最多用户数1. PL/SQL developer中查看2. Sqlplus中查看 2. 查看目前已经连接的用户数3. 修改用户连…...
react和antd学习笔记
概论 react是前端框架,antd是组件库。前端框架和组件库的区别与联系 nodejs 脚本语言需要一个解析器才能运行,JavaScript是脚本语言,在不同的位置有不一样的解析器,如写入html的js语言,浏览器是它的解析器角色。而对…...
寒假作业2月5号
第四章 堆与拷贝构造函数 一 、程序阅读题 1、给出下面程序输出结果。 #include <iostream.h> class example {int a; public: example(int b5){ab;} void print(){aa1;cout <<a<<"";} void print()const {cout<<a<<endl;} …...
滑动窗口(一)
文章目录 Leetcode209. 长度最小的子数组题目解法一(暴力求解)(超时)解法二(滑动窗口) Leetcode3. 无重复字符的最长子串题目解法一(暴力求解)解法二(滑动窗口) Leetcode1004. 最大连…...
寒假 day1
1、请简述栈区和堆区的区别? 2、有一个整形数组:int arr[](数组的值由外部输入决定),一个整型变量: x(也 由外部输入决定)。要求: 1)删除数组中与x的值相等的元素 2)不得创建新的数组 3)最多只允许使用单层循环 4)无需考虑超出新数组长度后面的元素,所以…...
DATAX改造支持geometry类型数据同步
数据库使用postgresql安装了postgis插件存储了geometry空间数据,想使用datax做数据同步,但datax本身不支持geometry类型数据,如何改造呢? 1.首先下载已改造支持geometry类型的datax引擎,下载地址 https://download.c…...
Vue中keep-alive的作用、原理及应用场景
在进行Vue开发的过程中,我们经常会遇到需要进行组件缓存的场景,这时候Vue提供的keep-alive组件就派上了用场。keep-alive组件是Vue内置的一个抽象组件,它可以将其包裹的组件进行缓存,提高组件的性能,同时也可以节省服务…...
SpringBoot集成Redisson实现限流(二)
1. 简介 Springboot集成Redisson默认的限流器为令牌桶型限流器,底层是通过lua脚本去实现的。 通过lua脚本我们可以去实现一个滑动窗口限流器,利用ZSET格式数据就可以轻松实现。 springboot集成Redisson就不做讲解,可以参考:sprin…...
【2024美赛E题】985博士解题思路分析(持续更新中)!
【2024美赛E题】985博士解题思路分析! 加群可以享受定制等更多服务,或者搜索B站:数模洛凌寺 联络组织企鹅:936670395 以下是E题老师的解题思路(企鹅内还会随时更新文档): 2024美赛E题思路详解…...
北朝隋唐文物展亮相广西,文物预防性保护网关保驾护航
一、霸府名都——太原博物馆收藏北朝隋朝文物展 2月1日,广西民族博物馆与太原博物馆携手,盛大开启“霸府名都——太原博物馆北朝隋文物展”。此次新春展览精选了北朝隋唐时期150多件晋阳文物珍品。依据“巍巍雄镇”“惊世古冢”“锦绣名都”三个单元&am…...
回归预测 | Matlab实现WOA-CNN-LSTM-Attention鲸鱼算法优化卷积长短期记忆网络注意力多变量回归预测(SE注意力机制)
回归预测 | Matlab实现WOA-CNN-LSTM-Attention鲸鱼算法优化卷积长短期记忆网络注意力多变量回归预测(SE注意力机制) 目录 回归预测 | Matlab实现WOA-CNN-LSTM-Attention鲸鱼算法优化卷积长短期记忆网络注意力多变量回归预测(SE注意力机制&…...
ubuntu离线安装k8s
目录 一、前期准备 二、安装前配置 三、安装docker 四、安装cri-dockerd 五、部署k8s master节点 六、整合kubectl与cri-dockerd 七、网络等插件安装 八、常见问题及解决方法 一、前期准备 ①ubuntu系统 本地已安装ubuntu系统,lsb_release -a命令查看版本信…...
学成在线:媒体资源管理系统(MAM)
媒体资源管理系统(MAM) 媒体资源管理系统(Media Asset Management)是建立在多媒体、网络、数据库和数字存储等先进技术基础上的一个对各种媒体及内容进行数字化存储、管理以及应用的总体解决方案,可以满足媒体资源拥有者收集、保存、查找、编辑、发布各种信息的要求,为媒体资源…...
18个8年以上服务器开发经验的面试题(2)
目录 1.问:如何设计一个系统来确保在可能出现网络分区和故障的分布式环境中的数据一致性?...
【SpringBoot】applicationContext.getBeansOfType(class)获取某一接口所有实现类,应用于策略模式
一、问题的提出 在实际工作中,我们经常会遇到一个接口及多个实现类的情况,并且在不同的条件下会使用不同的实现类。 二、应用场景 springboot 项目中通过 ApplicationContext.getBeansOfType(class) 获取某一接口的所有实现类,并通过枚举完…...
网络六边形受到攻击
大家读完觉得有帮助记得关注和点赞!!! 抽象 现代智能交通系统 (ITS) 的一个关键要求是能够以安全、可靠和匿名的方式从互联车辆和移动设备收集地理参考数据。Nexagon 协议建立在 IETF 定位器/ID 分离协议 (…...
JVM暂停(Stop-The-World,STW)的原因分类及对应排查方案
JVM暂停(Stop-The-World,STW)的完整原因分类及对应排查方案,结合JVM运行机制和常见故障场景整理而成: 一、GC相关暂停 1. 安全点(Safepoint)阻塞 现象:JVM暂停但无GC日志,日志显示No GCs detected。原因:JVM等待所有线程进入安全点(如…...
python执行测试用例,allure报乱码且未成功生成报告
allure执行测试用例时显示乱码:‘allure’ �����ڲ����ⲿ���Ҳ���ǿ�&am…...
MySQL账号权限管理指南:安全创建账户与精细授权技巧
在MySQL数据库管理中,合理创建用户账号并分配精确权限是保障数据安全的核心环节。直接使用root账号进行所有操作不仅危险且难以审计操作行为。今天我们来全面解析MySQL账号创建与权限分配的专业方法。 一、为何需要创建独立账号? 最小权限原则…...
JVM虚拟机:内存结构、垃圾回收、性能优化
1、JVM虚拟机的简介 Java 虚拟机(Java Virtual Machine 简称:JVM)是运行所有 Java 程序的抽象计算机,是 Java 语言的运行环境,实现了 Java 程序的跨平台特性。JVM 屏蔽了与具体操作系统平台相关的信息,使得 Java 程序只需生成在 JVM 上运行的目标代码(字节码),就可以…...
人工智能(大型语言模型 LLMs)对不同学科的影响以及由此产生的新学习方式
今天是关于AI如何在教学中增强学生的学习体验,我把重要信息标红了。人文学科的价值被低估了 ⬇️ 转型与必要性 人工智能正在深刻地改变教育,这并非炒作,而是已经发生的巨大变革。教育机构和教育者不能忽视它,试图简单地禁止学生使…...
排序算法总结(C++)
目录 一、稳定性二、排序算法选择、冒泡、插入排序归并排序随机快速排序堆排序基数排序计数排序 三、总结 一、稳定性 排序算法的稳定性是指:同样大小的样本 **(同样大小的数据)**在排序之后不会改变原始的相对次序。 稳定性对基础类型对象…...
20个超级好用的 CSS 动画库
分享 20 个最佳 CSS 动画库。 它们中的大多数将生成纯 CSS 代码,而不需要任何外部库。 1.Animate.css 一个开箱即用型的跨浏览器动画库,可供你在项目中使用。 2.Magic Animations CSS3 一组简单的动画,可以包含在你的网页或应用项目中。 3.An…...
动态 Web 开发技术入门篇
一、HTTP 协议核心 1.1 HTTP 基础 协议全称 :HyperText Transfer Protocol(超文本传输协议) 默认端口 :HTTP 使用 80 端口,HTTPS 使用 443 端口。 请求方法 : GET :用于获取资源,…...
纯 Java 项目(非 SpringBoot)集成 Mybatis-Plus 和 Mybatis-Plus-Join
纯 Java 项目(非 SpringBoot)集成 Mybatis-Plus 和 Mybatis-Plus-Join 1、依赖1.1、依赖版本1.2、pom.xml 2、代码2.1、SqlSession 构造器2.2、MybatisPlus代码生成器2.3、获取 config.yml 配置2.3.1、config.yml2.3.2、项目配置类 2.4、ftl 模板2.4.1、…...
