SQL Server 触发器

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: SQL Server 触发器

                      SQL Server 触发器


 

什么是触发器

1.触发器是对表进行插入、更新、删除操作时自动执行的存储过程

2.触发器通常用于强制业务规则

3.触发器是一种高级约束,可以定义比check等约束更为复杂的约束

 可执行复杂的sql语句(if/while/case)

 可引用其它表中的列

4.触发器定义在特定的表上,与表相关

5.自动触发执行,不能直接调用

6.是一个事务(可回滚,不能手动提交)

 

触发器的类型

delete触发器当删除表中记录时触发,自动执行触发器所定义的SQL语句

insert触发器当向表中插入数据时触发,自动执行触发器所定义的SQL语句

update触发器当更新表中某列、多列时触发,自动执行触发器所定义的SQL语句

 

deletedinserted

 

触发器触发时

 1.系统自动在内存中创建deleted表或inserted

 2.只读,不允许修改;触发器执行完后,自动删除

 

inserted:用于存储INSERTUPDATE语句所影响的行的副本。

1.在执行INSERTUPDATE语句时,新加行被同时添加到inserted表和触发器表中,所以inserted表临时保存了插入或更新后的记录行

2.可以从inserted中检查插入的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作

 

deleted表:表用于存储DELETEUPDATE语句所影响的行的副本

1.在执行DELETEUPDATE语句时,行从触发器表中删除,并传输到deleted表中,所以deleted表临时保存了删除或更新前的记录行

2.可从deleted表中检查被删除的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作

 

注意:更新(UPDATE)语句类似于在删除之后执行插入;首先旧行被复制到deleted表中,然后新行被复制到触发器表和inserted表中


 

如何创建触发器

1.创建触发器的语法

create trigger trigger_name

on table_name

for [delete|insert|update]

as

t-sql语句

go

 

2.insert触发器的工作原理

 

 

说明:

1. 执行insert插入语句,在表中插入数据行

2. 触发insert触发器,向系统临时表inserted表中插入新行的副本

3. 触发器检查inserted表中插入的新行数据,确定是搜需要回滚或执行其他操作。

问题:

解决上述的银行取款问题:当向交易信息表(transInfo)中插入一条交易信息时,我们应自动更新对应帐户的余额。

分析:

1.在交易信息表上创建INSERT触发器

2.inserted临时表中获取插入的数据行

3.根据交易类型(transType)字段的值是存入/支取,

4.增加/减少对应帐户的余额。

create trigger trig_transInfo

on transInfo

for insert

as

declare @type char(4),@outMoney money

declare @myCardID char(10),@blance money

#inserted表中获取交易类型、教员金额等

select @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted

#根据交易类型,减少或增加对应卡号的余额

if(@type=’支取’)

update bank set currentMoney=currentMoney-@outMoney where cardID=@myCardID

else

update bank set currentMoney=currentMoney+@outMoney where cardID=@myCardID

……

go

 

 

 

delete触发器

问题

当删除交易信息表时,要求自动备份被删除的数据到表backupTable

分析:

在交易信息表上创建delete触发器

被删除的数据可从deleted表中获取

注:delete删除触发器的典型应用就是银行系统中的数据备份。当交易记录过多时,为了不影响数据访问的速度,交易信息表需要定期删除部分数据。当删除数据时,一般需要自动备份,以便将来的客户查询、数据恢复或年终统计等。

 

create trigger trig_delete_transInfo

 

on transInfo

for delete

as

print’开始备份数据,请稍后……’

if not exists(select * from sysobjects where name=’backupTable’)

select * into backupTable from deleted

else

insert into backupTable select  * from deleted

print’备份数据成功,备份表中的数据为:

select * from backupTable

go


update触发器

update触发器的工作原理

 

 


 

说明:

执行更新操作,例如把李四的余额改为20001元。

更新操作可以看出两步:

1.删除李四原有的数据:李四1000 0002  1,将数据备份到deleted表中。

2.再插入新行:李四1000 0002  20001将数据备份到inserted表中。

 

最后看起来就是把余额从1修改为20001了。

所以:

如果我们希望查看修改前的原始数据,可以查看表deleted

如果我们希望查看修改后的数据,可以查看表inserted

 

问题:跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示。                  

分析:

bank表上创建UPDATE触发器

修改前的数据可以从deleted表中获取;修改后的数据可以从inserted表中获取

注解:update更新触发器主要用于跟踪数据的变化。典型的应用就是银行系统中,为了安全起见,一般要求每次交易金额不能超过一定的数额。

 

用 户每次的交易金额,我们可以从交易信息表中直接获取,也可以根据帐号信息表中余额的变化来获取。交易的方式较多,用户可能用卡消费,也可能用存折消费,存 折的交易信息与卡略有不同,可能不会将交易信息存放在交易表中,而保存在其它表中。但存折和卡对应的帐号余额只有一个。所以更安全的方案就是:根据账户信 息表中余额的变化来获取交易金额。为了获取交易余额的变化,我们应该在账户的信息表上创建update触发器

create trigger trig_updadte_bank

on bank

for update

as

declare @beforeMoney money,@after Money money

select @beforeMoney=currentMoney from deleted

select @afterMoney=currentMoney from inserted

if abs(@afterMoney-@beforMoney)>20000

begin

print’交易金额:’+convert(varchar(8),abs(beforeMoney-afterMoney))

raiserror(‘每笔交易不能超过2万元,交易失败’,16,1)

rollback transtraction

end

go

 

 

 


UPDATE触发器除了跟踪数据的变化(修改)外,还可以检查是否修改了某列的数据

使用UPDATE(列)函数检测是否修改了某列

问题:

交易日期一般由系统自动产生,默认为当前日期。为了安全起见,一般禁止修改,以防舞弊

UPDATE(列名)函数可以检测是否修改了某列

注:UPDATE( )函数:测试在指定的列上进行的INSERTUPDATE修改。

 

create trigger trig_update_transInfo

on transInfo

for update

as

if update(transDate) --检查是否修改了交易日期列transDate

begin

print’交易失败

raiserror(‘安全警告:交易日期不能修改,由系统自动产生’,16,1)

rollback transtraction

end

go

 


总结:

触发器是在对表进行插入、更新或删除操作时自动执行的存储过程,触发器通常用于强制业务规则

触发器还是一个特殊的事务单元,当出现错误时,可以执行ROLLBACK TRANSACTION回滚撤销操作

触发器一般都需要使用临时表:deleted表和inserted表,它们存放了被删除或插入的记录行副本

触发器类型:INSERT触发器、UPDATE触发器、DELETE触发器



相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
4月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
8天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 存储 数据管理
深入理解SQL中的触发器
【8月更文挑战第31天】
18 0
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
68 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
48 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
232 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
151 1
|
3月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
170 3
|
3月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。

热门文章

最新文章

下一篇
无影云桌面