大家好，又见面了，我是你们的朋友全栈君。
一、触发器
触发器（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表中检查被删除的数据是否满足业务需求； 如果不满足，则向用户报告错误消息，并回滚插入操作。
【创建触发器】
代码语言：javascript复制 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:创建一个特定标记符，只允许部分回滚。
锁是一种防止在某对象执行动作的一个进程与已在该对象上执行的其他进行相冲突的机制。也就是说， 如果有其他人在操作某个对象，那么你旧不能在该对象上进行操作。你能否执行操作取决于其他用户正在进 行的操作。
代码示例
代码语言：javascript复制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)锁、键范围锁发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/170886.html原文链接：https://javaforall.cn
