sqlserver事务锁死_sql触发器格式

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

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)锁、键范围锁