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