SqlServer中触发器的使用

--案例表
USE stuDB 
GO
CREATE  TABLE  stuInfo
(
stuName  varchar(20)  not null ,
stuNo   char(6)  PRIMARY KEY,
stuAge  int not null check(stuAge>0 and stuAge<100), 
stuID  varchar(18) not null, 
stuSeat   int identity(1,1),
stuAddress   varchar(50) default '住址不详'
)
GO
insert into stuInfo(stuName,stuNo,stuAge,stuId)
select '1','010001',21,'421990198909112311' union
select '2','010002',22,'421990198909111342' union
select '3','010003',23,'421990198909111242' union
select '4','010004',21,'421990198909111278' union
select '5','010005',22,'421990198909114556' union
select '6','010006',23,'421990198909117845' union
select '7','010007',24,'421990198909112345' union
select '8','010008',20,'421990198909117457' union
select '9','010009',22,'421990198909111557' union
select '蒋雯丽','010010',20,'421990198909111905' 
go
CREATE TABLE stuMarks
(
ExamNo  CHAR(7)  primary key,
stuNo  CHAR(6)  NOT NULL references stuInfo(stuNo),
writtenExam  INT  NOT NULL,
LabExam  INT  NOT NULL
)
GO
insert into stuMarks
	select '09001','010001',58,68 union
	select '09002','010002',66,77 union
	select '09003','010003',86,45 union
	select '09004','010004',62,62 union
	select '09005','010005',67,54 union
	select '09006','010006',78,69 union
	select '09007','010007',60,83 union
	select '09008','010008',48,74 union
	select '09009','010009',54,69 union
	select '09010','010010',61,55 

--创建登录触发器
--限制sa用户只能登陆3次
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'sa') > 3
    ROLLBACK;
END;

use BOOK
go
create trigger create_trigger
on database
for create_table
as
	print '正在创建表'
go

--禁止用户删除和修改表
use BOOK
go
create trigger deny_drop_table
on database
for drop_table,alter_table
as
begin
	print '不允许删除和修改表'
	rollback tran
end
--测试触发器
alter table test add tname varchar(20) not null
--禁用DDL触发器
disable trigger 触发器名 on all server
disable trigger 触发器名 on database
--启用DDL触发器
enable trigger 触发器名 on all server
enable trigger 触发器名 on database
--删除DDL触发器
drop trigger 触发器名 on all server
drop trigger 触发器名 on database

--after insert触发器
select * from stuinfo
select * from stumarks
--限制用户插入年龄》30 或《18的信息
--删除触发器
drop trigger check_insert_stuinfo

create trigger check_insert_stuinfo
on stuinfo
for insert
as
begin
	declare @age int
	--获取当前用户插入的数据
	select @age=stuage from inserted
	--判断年龄信息
	if @age>30 or @age<18
	begin
		raiserror('年龄数据必须在18-30之间',16,1)
		rollback tran
	end
end

--测试触发器
insert into stuinfo(stuname,stuno,stuage,stuid,stuaddress)
	values('CCC','010014',22,'555666',default)
SELECT * FROM STUINFO

--After delete触发器
--禁止用户删除信息
create trigger deny_delete_stuinfo
on stuinfo
for delete
as
begin
	declare @name varchar(20)
	select @name=stuname from deleted
	if @name='李斯文' or @name='梅超风'
	begin
		raiserror('不允许删除指定的学员信息',16,1)
		rollback tran
	end
end

--备份删除的信息
--创建备份表
select * into StuBak from stuinfo where 1=2
	--删除列stuseat
	alter table stubak drop column stuseat
	--添加列stuseat
	alter table stubak add stuseat int 

--创建触发器
create trigger delete_bak_stuinfo
on stuinfo
for delete
as
	insert into stubak(stuname,stuno,stuage,stuid,stuseat,stuaddress) select * from deleted

--测试	
delete from stuinfo where stuage>30
select * from stubak


--After update触发器
create trigger update_stumarks
on stumarks
for update
as
begin
	--如何判断有没有更新writtenEXAM和labExam
	if update(writtenExam) or update(labExam)
	begin
		raiserror('成绩字段不能为更新',16,1)
		rollback tran
	end
end
--测试触发器
update stumarks set labexam=labexam+10

--日志审计
create table tb_log
(
	log_id int identity(1,1) primary key,
	username varchar(20) not null,
	log_date datetime,
	log_desc varchar(100)
)
create trigger log_trigger
on stuinfo
for insert,delete,update
as
	--获取当前登录用户
	declare @name varchar(20)
	set @name=ORIGINAL_LOGIN()
	--获取当前操作时间
	declare @date datetime
	set @date=getdate()
	declare @desc varchar(100)
	if exists(select * from inserted) and not exists(select * from deleted)
	set @desc='插入数据'
	else if(exists(select * from deleted) and not exists(select * from inserted))
	set @desc='删除数据'
	else
	set @desc='修改数据'
	insert into tb_log values(@name,@date,@desc)
go

insert into StuInfo(stuname,stuno,stuage,stuid) 
	values('AAA','001',21,'123456')
select * from tb_log

--instead of触发器
create table stu
(
	sid int,
	sname varchar(20)
)
create table computer
(
	sid int,
	marks float
)
insert into stu values(1,'AAA')
insert into stu values(2,'BBB')
insert into stu values(3,'CCC')

insert into computer values(1,'60')
insert into computer values(2,'70')
insert into computer values(3,'80')
select * from stu
select * from computer
--创建视图
create view view_stu_computer
as
	select stu.sid,sname,marks from stu,computer
	where stu.sid=computer.sid
go
--查询视图
--视图基于一张表创建，可以对视图实施增、删、改操作
--视图基于多张表创建，不允许对视图实施。。。（在视图上创建instead of触发器）
select * from view_stu_computer
insert into view_stu_computer values(4,'DDD',90)
delete from view_stu_computer where sid=4

create trigger insert_view_stu_computer
on view_stu_computer
instead of insert
as
	--从inserted表中获取插入的数据
	declare @id int,@name varchar(20),@marks float
	select @id=sid,@name=sname,@marks=marks from inserted
	--向基表中插入数据
	insert into stu values(@id,@name)
	insert into computer values(@id,@marks)
go

create trigger delete_view_stu_computer
on view_stu_computer
instead of delete
as
	--从deleted表中获取正在删除的编号
	declare @id int
	select @id=sid from deleted
	--从基表删除数据
	delete from computer where sid=@id
	delete from stu where sid=@id
go