【基础7】SQL事务及触发器-Toy模板网

这篇具有很好参考价值的文章主要介绍了【基础7】SQL事务及触发器。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

事务（TRANSACTION）

触发器

DML触发器

DDL触发器

触发器的练习

事务（TRANSACTION）

什么是事务
- 事务是作为单个逻辑工作单元执行的一系列操作，它包含了一组数据库操作命令
- 所有的操作命令作为一个整体一起向系统提交，要么都执行、要么不执行
- 事是SQL Server 中执行并发控制的基本单位，是一个不可分隔的逻辑工作单元
事务的特性简称：ACID属性
- 原子性（Atomicity）
  
  事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么不执行
- 一致性（Consistency）
  
  当事务完成时，数据必须处于一致状态
- 隔离性（Isolation）
  
  对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，他不应该以任何方式依赖于或影响其他事务
- 永久性（Durability）
  
  事务完成后，他对数据库的修改被永久保持，事务日志能够保持事务的永久性
T-SQL使用下列语句来管理事务
- 开始事务： begin transaction
- 提交事务：commit transaction
- 回滚（撤销）事务： rollback transaction
一旦事务提交/回滚，则事务结束。
判断某条语句执行是否出
- 使用全局变量@@error
- @error只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL是否有错，需要对错误进行累计，如：
```
set @errorSum=@errorSum+@@error
```
事务的分类
- 显示事务：用 begin transaction 明确指定事务的开始，这是最常用的事务类型
- 隐性事务：通过设置set implicit_transactions on 语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。该事务完成时，再下一个T-SQL语句又将启动一个新事务
- 自动提交事务：这是SQL 的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚。

练习

if exists (select* from sysdatabases where name='bank')
drop database bank
go
create database bank
go

use bank
go

--建表
if exists (select * from sysobjects where name='cardinfo')
drop table cardinfo
go
create table cardinfo
(
customername  varchar(8) not null,
currentmoney money not null
)
go
--添加约束，卡里最少要有1元
alter table cardinfo
add constraint ck_currentmoney check (currentmoney>=1)
go
--插入数据
insert into cardinfo values ('张三', 1000)
insert into cardinfo values ('李四', 1)

select * from cardinfo 
delete from cardinfo
--张三给李四转账1000

update cardinfo set currentmoney -=1000 where customername='张三'
update cardinfo set currentmoney +=1000 where customername='李四'

--出现错误：张三由于表里的检查约束，没有转出去，而李四却收上了1000
--使用事务
set nocount on
print '转账前的数据：'
select * from cardinfo 
begin transaction		--事务开始
declare @error int		--定义一个便量
set @error=0
update cardinfo set currentmoney -=800 where customername='张三'
set @error += @@error
update cardinfo set currentmoney +=800 where customername='李四'
set @error += @@error
print '转账中'
if @error<>0
	begin
		print '转账失败，事务回滚'
		rollback transaction			--回滚
	end
else
	begin
		print '转账成功，事务提交'
		commit transaction			--提交
	end
print '转账后的数据：'
select * from cardinfo

触发器

触发器概述
- 触发器是一个特殊的存储过程，用于维护数据库中数据的完整性或执行一些特殊的规则
- 触发器是一个功能强大的工具，他与表格紧密相连，在表中数据发生变化时自动强制执行。
- 触发器是当发生DML或DDL语言事件时自动执行的存储过程，触发器不能直接被调用，也不能传递和接受参数。
触发器分类

DML触发器
- DML触发器：与表密不可分，触发器是不能离开表独立存在的，主要用于保护表中的数据，实现数据的完整性
- DML触发器分为after 触发器和 instead of 触发器
  
  after触发器在数据变动（insert、update、delete）完成后才被激发
  
  instead of 触发器在数据变动以前被激发，用来代替数据变动（insert、update、delete）操作，执行触发器定义操作
DDL触发器
- 用于管理数据库操作，响应数据定义语言（create、alter、drop、grant、revoke、deny、update statistics）等语句而触发。

创建触发器的语法

create trigger trigger_name   
on table_name|view_name|数据库
[with encryption]
{for|after|insterad of|instead of}											{[delete][,][insert][,][update]}
--触发时机--for和after都代表之后，instead of 代表之前					--触发事件
as
if update(列名)[and|or update(列名)][......n]			--执行任务

DML触发器

insert触发器

--在哪个表上插入信息就在哪个表上上建立触发器
--insert触发器（创建时会自动产生一个inserted表，表结构与所要插入信息表的结构完全一样）
use Sales
go

if exists (select * from sysobjects where name='tr_insert')
drop trigger tr_insert
go
create trigger tr_insert
on Employees
after insert 
as
print '刚刚插入的信息如下：'
select * from Employees where 编号=(select 编号 from inserted)
go

--插入信息测试
select * from Employees

insert into Employees values ('1305','李四',0,'销售部','01225252552','上海')
insert into Employees values ('1306','王五',0,'销售部','01225252552','上海')
insert into Employees values ('1307','张七',0,'销售部','01225252552','上海')


--超过库存的销售失败
if exists (select * from sysobjects where name='tr_insert1')
drop trigger tr_insert1
go
create trigger tr_insert1
on Sell
for insert 
as
if (select 数量 from inserted) > (select 数量 from Goods where 商品编号 in (select 商品编号 from inserted))
	begin
		print '销售数量>进货数量，销售失败！'
		rollback transaction
	end
else
	begin
		print'销售成功，销售后信息如下：'
		select * from Sell where 商品编号 in (select 商品编号 from inserted)
	end
go

--插入信息测试
select * from Goods
Select * from Sell

insert into Sell values(1,1,getdate(),'1301')

delete触发器

--delete 触发器（会创建一个临时表deleted，保存刚刚删除的信息的副本）
select * from Employees
Select * from Sell

if exists (select * from sysobjects where name='tr_delete')
drop trigger tr_delete
go
create trigger tr_delete
on Employees
for delete
as
if exists (select * from Sell where 售货员工编号=(select 编号 from deleted))
	begin
		print '当前员工存在销售信息，不允许删除！'
		rollback transaction
	end
else
	begin
		print '删除成功，删除信息如下：'
		select * from deleted
	end
go

--测试
delete from Employees where 编号='1307'

--及联删除
if exists (select * from sysobjects where name='tr_delete1')
drop trigger tr_delete1
go
create trigger tr_delete1
on Employees
with encryption
for delete
as
if exists (select * from Sell where 售货员工编号=(select 编号 from deleted))
	print '刚刚删除的信息如下'
	select * from deleted
	print '级联删除sell表中的数据'
	--delete from sell where 售货员工编号=(select 编号 from deleted)
	delete from sell from Sell,deleted  where deleted.编号=Sell.售货员工编号
	print '删除后sell表、Employees信息如下'
	select * from Employees
	select * from Sell
go

--测试
delete from Employees where 编号='1301'
delete from Employees where 编号='1302'
delete from Employees where 编号='1303'

约束的启用/禁用

--启用约束
alter table sell
check constraint fk_售货员工编号
go

--禁用约束
alter table sell
nocheck constraint fk_售货员工编号
go

update 触发器

--update 触发器（创建两张临时表：deleted/inserted）
--修改时:先删除--删除的数据放在deleted表中，后添加--添加的数据放在inserted表中
--将1303改为1304 ，从表里的1303也要改为1304
alter table Sell 
nocheck constraint fk_售货员工编号
go

set nocount on	--对整个表有作用

if exists (select * from sysobjects where name='tr_update')
drop trigger tr_update
go
create trigger tr_update
on Employees
with encryption
for update
as
set nocount on			--这个只对此触发器有作用
print '修改前员工信息如下：'
select * from deleted
print '修改后员工信息如下：'
select * from inserted
print '级联删除sell表中的相关数据：'
update Sell set 售货员工编号=(select 编号 from inserted) where 售货员工编号=(select 编号 from deleted)

print '修改后信息如下：'
select * from Employees
select * from Sell
go

--测试
update Employees set 编号='1304' where 编号='1303'


--不允许修改Employees表中的信息

if exists (select * from sysobjects where name='tr_update1')	--保护数据不被修改
drop trigger tr_update1
go
create trigger tr_update1
on Employees
with encryption
for update
as
if update (编号) or update (姓名) or update (性别)		--不允许修改这三列中的内容
begin
	print '不允许修改Employees表中的相关内容'
	rollback transaction
end
go

--测试
update Employees set 编号='1304' where 编号='1303'

触发器的启用与禁用

--禁用触发器
alter table Employees
disable trigger tr_delete
go

disable trigger tr_delete on Employees


--启用触发器
alter table Employees
enable trigger tr_delete
go

enable trigger tr_delete on Employees

instead of 替代触发器

--instead of 替代触发器（替代 insert、update、delete 操作）

if exists (select * from sysobjects where name='tr_instead')
drop trigger tr_instead
go
create trigger tr_instead
on Employees
instead of update
as
	begin
		print '不允许修改Employees表中的数据'
		rollback transaction
	end
go

--instead of 替代触发器 （替代在视图中不能进行的inser、update、delete 操作）--即有函数列、修改多个表的不能进行

select * from V_采购
select * from Goods

alter table Employees
nocheck constraint ck_性别
go

insert into V_采购 values ('1001','赵飞燕','1','gongsi',111.00,222.00,10)		--这个数据与视图中对应

if exists (select * from sysobjects where name='tr_instead1')
drop trigger tr_instead1
go
create trigger tr_instead1
on V_采购
instead of insert
as
print '添加数据完成后：'
insert into Goods values  ('wu','gong',111,222,10,getdate(),'1001')		--这个要与表中的对应
select * from V_采购
go

DDL触发器

针对数据库的触发器

--DDL 触发器 （create drop alter grant revoke deny）
--						创建  删除 修改  授权 撤销    否定(没有撤销，但是不能用了)

--针对数据库创建的DDL
if exists (select * from sys.triggers where name='tr_droptable')	--删除触发器
--select * from sys.triggers   正在使用的数据库的所有的触发器
drop trigger tr_droptable on database			--这里是数据库触发器，删除时要加 on database
go
create trigger tr_droptable 
on database 
for drop_table
as
	begin
		print '不允许删除数据表！'
		rollback transaction
	end
go

select * into sell1 from Sell   --复制表

select * from Sell1

drop table sell1

--不允许创建数据表
if exists (select * from sys.triggers where name='tr_createtable')
drop trigger tr_cteatetable on database		
go
create trigger tr_createtable 
on database 
for create_table
as
	begin
		print '不允许创建数据表！'
		rollback transaction
	end
go

--测试		只要是新建表都不行，复制也属于新建
select * into sell3 from Sell   --复制表
--触发器的禁用/启用
disable trigger tr_createtable on database
disable trigger tr_droptable on database

enable trigger tr_createtable on database
enable trigger tr_droptable on database

针对服务器的触发器文章来源地址https://www.toymoban.com/news/detail-457527.html


--针对服务器的DDL触发器
if exists (select * from sys.server_triggers where name='tr_dropdatabase')
drop trigger tr_dropdatabase on all server
go
create trigger tr_dropdatabase
 on all server
after drop_database
as
	begin
		print '不允许删除数据库'
		rollback transaction
	end
go

--测试
drop database abc
drop database bank


--创建		可以创建
create database bank

--不允许创建
if exists (select * from sys.server_triggers where name='tr_createdatabase')		
-- select * from sys.server_triggers     查看服务器上的触发器
drop trigger tr_createdatabase on all server
go
create trigger tr_createdatabase
 on all server
after create_database
as
	begin
		print '不允许创建数据库'
		rollback transaction
	end
go

--测试
create database bank1


--启用和禁用都要一个一个进行，不能两个一起
--禁用DDL触发器		ddl触发器是建在服务器上的，所以不能用表的那种方式修改  
disable trigger tr_createdatabase on all server	
disable trigger tr_dropdatabase on all server			

--启用
enable trigger tr_createdatabase on all server	
enable trigger tr_dropdatabase on all server

触发器的练习

use XK
go


select * from Student
select * from StuCou
select * from Course
select * from Class

--所有执行结果都不显示几行受影响
set nocount on

--1、在stucou表中，创建触发器tr_insert
--添加选课信息，添加后自动更新Course表中的选课人数列willnum    每人最多选课5门   对触发器创建的文本加密

if exists (select * from sysobjects where name='tr_insert')
drop trigger tr_insert
go
create trigger tr_insert on StuCou
with encryption
for insert
as
if (select count(*) from StuCou where StuNo=(select StuNo from inserted)) <= 5
	begin
		print '选课前信息 如下：'
		select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from inserted)
		print '选课成功！'
		print '成功更新选课信息表Course中的相应信息！'
		update Course set WillNum=WillNum + 1 where CouNo=(select CouNo from inserted)
		print '选课后信息如下：'
		select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from inserted)
		select * from StuCou where StuNo=(select StuNo from inserted)
	end
else
	begin
		print '选课门数已达上限！不能再选课！'
		rollback transaction
	end

--测试
insert into stucou values('00000002','002',5,'报名',null)
--禁用约束
alter table StuCou
nocheck constraint CK_WillOrder
go
--禁用后测试
insert into stucou values('00000001','016',6,'报名',null)

--2、在stucou表  创建触发器tr_update，将学生编号为'00000002'且课程号为’002’的课程号修改为’003’， 
--同时自动更新Course表中的选课人数列willnum。

if exists (select * from sysobjects where name='tr_update')
drop trigger tr_update
go
create trigger tr_update on StuCou
for update
as
print '修改前的数据如下：'  
select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from deleted)
select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from inserted)

print '成功更新选课信息表Course中的相应信息！'
update Course set WillNum=WillNum - 1 where CouNo=(select CouNo from deleted)
update Course set WillNum=WillNum + 1 where CouNo=(select CouNo from inserted)
print '修改后的数据如下：'
select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from deleted)
select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from inserted)

select * from StuCou where CouNo=(select CouNo from inserted) and StuNo=(select stuNo from inserted)
--老师的：
select StuCou.* from StuCou inner join inserted on StuCou.StuNo=inserted.StuNo and StuCou.CouNo=inserted.CouNo
go

--测试
update StuCou set CouNo='003' where CouNo='002' and StuNo='00000002'

--3、在stucou表中，创建触发器tr_delete，将学生编号为'00000002'且课程号为’002’的选课信息删除
--应该是003的，2题已改002为003
--同时自动更新Course表中的选课人数列willnum。

if exists (select * from sysobjects where name='tr_delete')
drop trigger tr_delete
go
create trigger tr_delete on StuCou
for delete
as
print '删除前的数据如下：'  
select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from deleted)

print '成功更新选课信息表Course中的相应信息！'
update Course set WillNum=WillNum - 1 where CouNo=(select CouNo from deleted)
print '删除后的数据如下：'

select CouNo ,CouName ,Teacher,LimitNum ,WillNum from Course where CouNo=(select CouNo from deleted)
select * from StuCou where StuNo=(select StuNo from deleted)


--测试
delete from StuCou where StuNo='00000002' and CouNo='003'



--4、在stucou表中，创建触发器tr_instead，不允许删除选课表中数据。
--错过，改了的
if exists (select * from sysobjects where name='tr_instead')
drop trigger tr_instead
go
create trigger tr_instead
on StuCou
instead of delete
as
if exists (select * from StuCou where StuNo in (select StuNo from deleted))  
--declare @id varchar(8)
--set @id=(select StuNo from deleted)
--if @id in (select StuNo from StuCou)     可用注释的三行代替存在子判断
	begin
		print '不允许删除选课信息'
		rollback transaction
	end
else
	print '没有数据被删除'
go

--测试
delete from stucou where stuno='00000002' and couno='001'

delete from stucou where stuno='000000020' and couno='001'--不会

--5、创建一个DDL触发器trigger_droptable，防止当前数据库的数据表被删除
if exists (select * from sys.triggers where name='tr_droptable')	
drop trigger tr_droptable on database	
go
create trigger tr_droptable 
on database 
for drop_table
as
	begin
		print '不允许删除数据表！'
		rollback transaction
	end
go
--测试
drop table StuCou


--6、创建一个DDL触发器trigger_dropdatabase，防止当前服务器中的数据库被删除
if exists (select * from sys.server_triggers where name='tr_dropdatabase')
drop trigger tr_dropdatabase on all server
go
create trigger tr_dropdatabase on all server
after drop_database
as
	begin
		print '不允许删除数据库'
		rollback transaction
	end
go

drop database Sales


--7、查看触发器tr_insert的创建文本。
exec sp_helptext tr_insert

--8、试用两种方法禁用tr_instead触发器，执行下列语句，进行选课信息的删除和添加，执行完成后，重新启用触发器。
	--禁用触发器1
	alter table StuCou
	disable trigger tr_instead
	go
	--2
	disable trigger tr_instead on StuCou

delete from stucou where stuno='00000002' and couno='001'
insert into stucou values('00000002','001',5,'报名',null)


--9、启用tr_droptable和tr_dropdatabase触发器
enable trigger tr_droptable on database
enable trigger tr_dropdatabase on all server

--10、删除所创建的tr_insert、tr_update、tr_delete、tr_instead、tr_droptable和tr_dropdatabase触发器
drop trigger tr_insert
drop trigger tr_update
drop trigger tr_delete
drop trigger tr_instead
drop trigger tr_droptable on database
drop trigger tr_dropdatabase on all server

到了这里，关于【基础7】SQL事务及触发器的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！