SQL触发器的简单运用

--触发器
1,是特殊的存储过程,在修改表时候自动执行
2,一般用于加强数据某些使用规则
3,触发器有两张表,有一张,插入表和删除表,每个触发器都创建这两张表,是临时逻辑表由系统维护不允许用户直接修改
4,临时逻辑表存在内存中,不在数据库中,与数据库中表结构是一样的
5,触发工作完成逻辑临时表被删除
6, inserted 表存放inserted 或update 语句执行所影响的行的副本,插入或修改行值
7,deleted ,存放,delete 或update 语句执行所影响行的副本,当执行delete 时候存在delete 表中
8,更新也就是修改有两个动作一个是删除一个添加,故存在两张逻辑表中
9,触发器不能创建在临时表上但是能够操作临时表

 

 

 

IF EXISTS (select * from sys.databases where name='netBarDB')
	drop database netBarDB
create database netBarDB
go
use netBarDB
go
create table cardInfo
(
	cardId int primary key identity,
	cardNo nvarchar(16),
	cardPassword nvarchar(16),
	transactTime datetime ,
	cardBalance money
)
go
create table PCUseInfo
(
	PCId int primary key identity,
	PCUse int,
	PCName nvarchar(16)
)
go
create table recordInfo--记录表
(
	recordId int primary key identity,
	cardId int,
	PCId int,
	beginTime dateTime,
	endTime dateTime,
	fee money
)
--drop table recordInfo1

go
--insert recordInfo values(1,'100-2',GETDATE(),null,null)
go
insert cardInfo values('100-1','123456',GETDATE(),0)
insert cardInfo values('100-20','123456',GETDATE(),1)
insert cardInfo values('100-18','123456',GETDATE(),100)
insert cardInfo values('100-8','123456',GETDATE(),20)
insert cardInfo values('100-9','123456',GETDATE(),30)
insert cardInfo values('100-10','123456',GETDATE(),40)
go
insert PCUseInfo values(0,'左边第一台')
insert PCUseInfo values(0,'左边第二台')
insert PCUseInfo values(0,'左边第三台')
insert PCUseInfo values(0,'左边第四台')
insert PCUseInfo values(0,'左边第五台')
insert PCUseInfo values(0,'左边第六台')
go
--当向上机记录表插入数据时候,自动更新机器状态
IF EXISTS (select * from sys.objects where name='tr_inersert_recordInfo')
	drop trigger tr_inersert_recordInfo
go
create trigger tr_inersert_recordInfo
on recordInfo  --表明在哪张表创建触发器
for insert --表明是什么操作什么触发器
as
			--定义变量接收电脑编号,和卡号
			declare @PCId int
			declare @cardId int
			declare @cardNo nvarchar(16)
			--添加时候查询inserted临时表,数据存在该临时表中
	        select @PCId=PCId, @cardId=cardId from inserted
			--根据电脑编号更改状态
			update PCUseInfo set PCUse=1 where PCId=@PCId
			--根据卡号的id得到卡号
			select @cardNo=cardNo from cardInfo where cardId=@cardId
			print '上机成功! 卡号:'+@cardNo+'    机器编号是:'+convert(nvarchar,@PCId)
			
go
--设置不显示受影响行数
set nocount on
--目的是,我在向上机记录表添加数据(就是新来的上机网友),上网时,自动更改对应电脑状态
insert recordInfo values(2,5,GETDATE(),null,null) -- 测试触发器

--创建删除触发器
--删除记录表中数据,备份以便以后查用
go
create trigger tr_delete_recordInfo
on recordInfo
for delete
as
	print '备份进行中,请稍等...........'
	if exists ( select * from sys.objects where name ='backrecordInfo' )
		insert into backrecordInfo select * from recordInfo
	else
		select * into backrecordInfo from deleted
	print '备份完成,已经备份到表:backrecordInfo'
	select * from backrecordInfo
go	
delete from recordInfo  --当记录表为空时会报错,思考如何解决

--创建更新触发器
--用户换机时候,修改上机表,和上机记录表
go
create trigger tr_update_recordInfo
on recordInfo
for update
as
	declare @beforPCId int,@afterPCId int
	declare @recordId int 
	select @beforPCId=PCId from deleted  --更改前电脑编号
	select @afterPCId=PCId from inserted --更改后的电脑编号
	--实现换机把自己使用的机器状态改为0,要上机的电脑改为1
	update PCUseInfo set PCUse=0 where PCId = @beforPCId
	update PCUseInfo set PCUse=1 where PCId=@afterPCId
	print '换机成功!已经从:'+convert(nvarchar,@beforPCId)+' 换到了: '+convert(nvarchar,@afterPCId)

go
--把机器从1号换到2号
update recordInfo set PCId=5 where PCId=3

--判断特定列是否被修改,或者只修改特定列才执行触发器
go
create trigger tr_updateColum_recordInfo
on recordInfo
for update
as
	if update(beginTime)
		begin
			print '安全警告!不能修改上机时间上机只能系统生成'
			rollback transaction --回滚事务撤销操作
		end
go
 update recordInfo set beginTime = '2015-10-1' where  recordId=1

 --替代触发器insted of
 --先触发,再执行sql,可以用于视图,默认的after 触发器不能用于视图,
 --数据库禁止修改的数据,避免回滚的SQL,优先用insted of 触发器更好
 --编写触发器,在余额小于2元不能上机
 go
 create trigger tr_insert_recordInfo
 on recordInfo
 instead of insert
 as
	declare @cardBalance money
	declare @pcId int
	declare @cardId int
	select  @pcId=pcId, @cardId=cardId from inserted
	select @cardBalance=cardBalance from cardInfo where cardId=@cardId
	if (@cardBalance<2)
		print '余额不足两元不能上机!请充值'
	else
		begin
			update PCUseInfo set pcUse=1 where pcid=@pcId
		end
 go
 --实现100-1在5号机上机
 declare @cardId int
 select @cardId=cardId from cardInfo where cardNo='100-1'
 insert recordInfo values(@cardId,5,GETDATE(),GETDATE(),1)
 --向上机记录表插入数据
 insert recordInfo values(@cardId,5,GETDATE())
select * from cardInfo
select * from PCUseInfo
select * from recordInfo
drop trigger tr_insert_recordInfo