SQL Server——SQL Server触发器及事务和锁

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: SQL Server——SQL Server触发器及事务和锁

一、触发器


触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表 事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表 进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务 规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。 【触发器和存储过程的区别】 触发器与存储过程的区别是运行方式的不同,触发器不能执行EXECUTE语句调用,而是在用户执行 Transact-SQL语句时自动触发执行而存储过程需要用户,应用程序或者触发器来显示地调用并执行。


【触发器的优点】


a.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。 b.触发器可以通过数据库中的相关表进行层叠修改。 c.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可 以引用其他表中的列。


【触发器的作用】


触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够 对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。 触发器的主要作用主要有以下接个方面: 强制数据库间的引用完整性 级联修改数据库中所有相关的表,自动触发其它与之相关的操作 跟踪变化,撤销或回滚违法操作,防止非法修改数据 返回自定义的错误消息,约束无法返回信息,而触发器可以 触发器可以调用更多的存储过程


【触发器的分类】


SqlServer包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。


1.DML(数据操作语言,Data Manipulation Language)触发器


DML触发器是一些附加在特定表或视图上的操作代码,当数据库服务器中发生数据操作语言事件时执 行这些操作。


SqlServer中的DML触发器有三种:


insert触发器:向表中插入数据时被触发;


delete触发器:从表中删除数据时被触发;


update触发器:修改表中数据时被触发。


当遇到下列情形时,应考虑使用DML触发器: 通过数据库中的相关表实现级联更改 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他 限制。 评估数据修改前后表的状态,并根据该差异才去措施。


2.DDL(数据定义语言,Data Definition Language)触发器


DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时 被激活使用,使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。


【触发器的工作原理】


触发器触发时: 系统自动在内存中创建deleted表或inserted表;只读,不允许修改,触发器执行完成后,自动删除。


inserted表: 临时保存了插入或更新后的记录行; 可以从inserted表中检查插入的数据是否满足业务需求; 如果不满足,则向用户发送报告错误消息,并回滚插入操作。


deleted表: 临时保存了删除或更新前的记录行; 可以从deleted表中检查被删除的数据是否满足业务需求; 如果不满足,则向用户报告错误消息,并回滚插入操作。


【创建触发器】


 CREATE TRIGGER trigger_name 
 ON table_name 
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] 
 AS
 GO 


T-SQL语句


– with encryption 表示加密触发器定义的sql文本


– delete,insert,update指定触发器的类型


二、事务和锁


事务是SQL Server中单个的逻辑工作单元,该单元被作为一个整体进行处理,事务保证连续多个操 作必须全部执行成功,否则必须立即恢复到任何操作执行前的状态,即执行事务的结果是要么全部将数据所 要执行的操作完成,要么全部数据修改。


事务作为单个逻辑工作单元执行的一系列操作,事务的处理必须满足ACID原则。


BEGIN TRAN:设置起始点。


COMMIT TRAN:使事务成为数据库中永久的、不可逆转的一部分。 ROLLBACK TRAN:本质上说想要忘记它曾经发生过。


SAVE TRAN:创建一个特定标记符,只允许部分回滚。


锁是一种防止在某对象执行动作的一个进程与已在该对象上执行的其他进行相冲突的机制。也就是说, 如果有其他人在操作某个对象,那么你旧不能在该对象上进行操作。你能否执行操作取决于其他用户正在进 行的操作。


代码示例


use testdbs
go
-- 一、操作之前准备的数据
CREATE TABLE student 
(
   stno char(6) NOT NULL PRIMARY KEY,
   stname char(8) NOT NULL,
   stsex char(2) NOT NULL,
   stbirthday date NOT NULL,
   speciality char(12) NULL,
   tc int NULL
)
GO
CREATE TABLE course
(
   cno char(3) NOT NULL PRIMARY KEY,
   cname char(16) NOT NULL,
   credit int NULL,
   tno char (6) NULL,
)
GO
CREATE TABLE score
(
   stno char (6) NOT NULL,
   cno char(3) NOT NULL,
   grade int NULL,
   PRIMARY KEY(stno,cno)
)
GO
CREATE TABLE teacher
(
   tno char (6) NOT NULL PRIMARY KEY,
   tname char(8) NOT NULL,
   tsex char (2) NOT NULL,
   tbirthday date NOT NULL,
   title char (12) NULL,
   school char (12) NULL
)
GO
-- 测试数据
INSERT INTO student values('121001','李贤友','男','1991-12-30','通信工程',72),
('121002','周映雪','女','1993-01-12','通信工程',89),
('121005','刘刚','男','1992-07-05','通信工程',59),
('122001','郭德强','男','1991-10-23','计算机',58),
('122002','谢萱','女','1992-09-11','计算机科',77),
('122004','孙婷','女','1992-02-24','计算机',63);
INSERT INTO student values('121015','晴天','男','2000-12-30','软件工程',92),
('121022','MM','女','1983-01-12','数学',99),
('121065','大猫','男','1902-07-05','物理',86),
('122031','小强','男','1981-10-23','数学',78),
('122302','周芳','女','1982-09-11','物理',88),
('122804','王鑫','女','1983-02-24','软件工程',79);
GO
INSERT INTO course values('102','数字电路',3,'102101'), ('203','数据库系统',3,'204101'),
('205','微机原理',4,'204107'),('208','计算机网络',4,NULL),('801','高等数学',4,'801102')
GO
INSERT INTO score values('121001','102',92),('121002','102',72),('121005','102',87),('122002','203',94),
('122004','203',81),('121001','205',91),('121002','205',65),('121005','205',85),('121001','801',94),
('121002','801',73),('121005','801',82),('122001','801',NULL),('122002','801',95),('122004','801',86);
GO
INSERT INTO teacher values('102101','刘林卓','男','1962-03-21','教授','通信学院'),
('102105','周学莉','女','1977-10-05','讲师','通信学院'),
('204101','吴波','男','1978-04-26','教授','计算机学院'),
('204107','王冬琴','女','1968-11-18','副教授','计算机学院'),
('801102','李伟','男','1975-08-19','副教授','数学学院');
GO
-- 查询数据
select *from student
select *from course
select *from score
select *from teacher
----------------------------------------------------------------------------------------------------
-- 二、使用T-SQL创建触发器
select *from student
go
-- 1、在student表上创建触发器trig_student,在student表中插入、删除、修改数据它会自动显示所有记录
-- create trigger必须是批处理的第一条语句,此处go不能缺少
create trigger trig_student
  on student
after insert,delete,update
as
begin
  set nocount on  --在存储过程中经常使用到的,阻止在结果集中显示受T-SQL语句或则USP影响的行计数信息:set nocount on不返回计算,set nocount off返回计数
  select *from student
end
go
-- 测试数据
insert into student values('126088','小张','女','2003-8-8','文秘',78)
go
-- 2、在student表上创建insert触发器,当向student插入数据时如果姓名发生重复时,则回流到插入之前的操作
create trigger trig_studentinsert
  on student
  after insert
as
begin
  declare @nm char(8)
  select @nm=inserted.stname from inserted
  if exists(select stname from student where stname=@nm)  
    begin 
    print '对不起,数据中存储此姓名,请不要重复插入.'
    rollback transaction
    end
end
-- 测试数据
select *from student
INSERT INTO student(stno,stname,stsex,stbirthday) values('121888','青龙','男','2018-12-30')
go
-- 3、创建触发器防止用户修改学分
create trigger trig_updatestudent
  on student
after update
as
if UPDATE(tc)
  begin 
    print '数据表中学分非常重要,保密级别高,不允许用户修改学分.'
    rollback transaction  -- 回流之前的操作
  end
go
-- 测试数据
select *from student
update student 
set tc=50
where stno=121001
go
-- 4、在student表中,防止用户删除'通信工程'专业的学生记录信息
create trigger trig_deletestudent
  on student
after delete
as
  if exists(select *from deleted where speciality='计算机')
    begin
    print '禁止删除《计算机》专业的学生所有记录.'
    rollback transaction
    end
go
-- 测试数据
select *from student
delete student
where speciality='计算机科'
-- 5、在course表上创建一个insetead of触发器,当用户向此表插入数据时显示course表中的记录
select *from course
go
create trigger trig_istd
  on course
instead of insert
as 
  select *from course
go
-- 测试数据
select *from course
go
insert into course(cno,cname) values('206','数据结构')
go
----------------------------------------------------------------------------------------------------
-- 二、创建和使用DDL触发器
-- 1、创建一个解发器,防止用户对数据库中任何一个表进行修改或删除 
create trigger trig_db
  on database
after drop_table,alter_table
as
  begin
  print '不能修改数据表的结构'
  rollback transaction  -- 回滚之前操作
  end
go
-- 测试数据
select *from student
go
alter table student add class int
go
-- 删除DML触发器
-- drop trigger 触发器的名称
-- 删除DDL触发器
-- drop trigger 触发器的名称 on database
----------------------------------------------------------------------------------------------------
-- 三、事务处理
use bank
go
-- 查询数据
select *from tb_bank
go
-- 插入两条数据
insert into tb_bank values('1111222233334444678','曹操',100000)
insert into tb_bank values('1111222233334444123','刘备',50000)
-- 查询数据
select *from tb_bank
go
-- 事务银行转账操作
-- 查询数据
select *from tb_bank
go
begin tran tran_bankmoney --开始事务
declare @tran_error int;
set @tran_error=0
  begin try
    update tb_bank set accountbalance=accountbalance-150000 where accountname='曹操'
    set @tran_error=@tran_error+@@ERROR
    -- 测试出错代码,查询曹操的钱减少15000,刘备的钱是否增加15000
    -- set @tran_error=1
    update tb_bank set accountbalance=accountbalance+150000 where accountname='刘备'
    set @tran_error=@tran_error+@@ERROR
  end try
begin catch
  print '出现异常,错误编号:'+convert(varchar,error_number()) +',错误消息:'+error_message()
  set @tran_error=@tran_error+1
end catch
if (@tran_error>0)
  begin
    rollback tran
    print '转账失败,取消本次交易!'
  end
else
  begin
    commit tran
    print '转账成功,提交数据库!'
  end
go
-- 测试数据
select *from tb_bank
go
-- 锁
-- 锁定是SQL Server用来同步多个用户同时对同一个数据块的访问的一种机制,用于控制多个用户的并发操作,
-- 以防止用户读取到由其他用户更改的数据或者多个用户同时修改同一数据。从而确保事务的完整性和数据库的一致性。
-- 锁模式:共享锁(S锁)、更新锁(U锁)、排他锁、意向锁、架构锁、大容量更新(BU)锁、键范围锁
相关实践学习
使用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
相关文章
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1
|
4天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
23 3
|
9天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
11天前
|
SQL 数据库 数据安全/隐私保护
【Python】已解决:(SqlServer报错)SQL错误(208):对象名‘string_split’无效
【Python】已解决:(SqlServer报错)SQL错误(208):对象名‘string_split’无效
14 2
|
15天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
24 2
|
20天前
|
SQL 存储 测试技术
|
19天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
24天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
1月前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
59 3