--案例表USE stuDB GOCREATE 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 '住址不详')GOinsert into stuInfo(stuName,stuNo,stuAge,stuId)select '1','010001',21,'421990198909112311' unionselect '2','010002',22,'421990198909111342' unionselect '3','010003',23,'421990198909111242' unionselect '4','010004',21,'421990198909111278' unionselect '5','010005',22,'421990198909114556' unionselect '6','010006',23,'421990198909117845' unionselect '7','010007',24,'421990198909112345' unionselect '8','010008',20,'421990198909117457' unionselect '9','010009',22,'421990198909111557' unionselect '蒋雯丽','010010',20,'421990198909111905' goCREATE TABLE stuMarks(ExamNo CHAR(7) primary key,stuNo CHAR(6) NOT NULL references stuInfo(stuNo),writtenExam INT NOT NULL,LabExam INT NOT NULL)GOinsert into stuMarksselect '09001','010001',58,68 unionselect '09002','010002',66,77 unionselect '09003','010003',86,45 unionselect '09004','010004',62,62 unionselect '09005','010005',67,54 unionselect '09006','010006',78,69 unionselect '09007','010007',60,83 unionselect '09008','010008',48,74 unionselect '09009','010009',54,69 unionselect '09010','010010',61,55 --创建登录触发器--限制sa用户只能登陆3次CREATE TRIGGER connection_limit_triggerON ALL SERVERFOR LOGONASBEGINIF 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 BOOKgocreate trigger create_triggeron databasefor create_tableasprint '正在创建表'go--禁止用户删除和修改表use BOOKgocreate trigger deny_drop_tableon databasefor drop_table,alter_tableasbeginprint '不允许删除和修改表'rollback tranend--测试触发器alter table test add tname varchar(20) not null--禁用DDL触发器disable trigger 触发器名 on all serverdisable trigger 触发器名 on database--启用DDL触发器enable trigger 触发器名 on all serverenable trigger 触发器名 on database--删除DDL触发器drop trigger 触发器名 on all serverdrop trigger 触发器名 on database--after insert触发器select * from stuinfoselect * from stumarks--限制用户插入年龄》30 或《18的信息--删除触发器drop trigger check_insert_stuinfocreate trigger check_insert_stuinfoon stuinfofor insertasbegindeclare @age int--获取当前用户插入的数据select @age=stuage from inserted--判断年龄信息if @age>30 or @age<18beginraiserror('年龄数据必须在18-30之间',16,1)rollback tranendend--测试触发器insert into stuinfo(stuname,stuno,stuage,stuid,stuaddress)values('CCC','010014',22,'555666',default)SELECT * FROM STUINFO--After delete触发器--禁止用户删除信息create trigger deny_delete_stuinfoon stuinfofor deleteasbegindeclare @name varchar(20)select @name=stuname from deletedif @name='李斯文' or @name='梅超风'beginraiserror('不允许删除指定的学员信息',16,1)rollback tranendend--备份删除的信息--创建备份表select * into StuBak from stuinfo where 1=2--删除列stuseatalter table stubak drop column stuseat--添加列stuseatalter table stubak add stuseat int --创建触发器create trigger delete_bak_stuinfoon stuinfofor deleteasinsert into stubak(stuname,stuno,stuage,stuid,stuseat,stuaddress) select * from deleted--测试delete from stuinfo where stuage>30select * from stubak--After update触发器create trigger update_stumarkson stumarksfor updateasbegin--如何判断有没有更新writtenEXAM和labExamif update(writtenExam) or update(labExam)beginraiserror('成绩字段不能为更新',16,1)rollback tranendend--测试触发器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_triggeron stuinfofor insert,delete,updateas--获取当前登录用户declare @name varchar(20)set @name=ORIGINAL_LOGIN()--获取当前操作时间declare @date datetimeset @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='删除数据'elseset @desc='修改数据'insert into tb_log values(@name,@date,@desc)goinsert 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 stuselect * from computer--创建视图create view view_stu_computerasselect stu.sid,sname,marks from stu,computerwhere stu.sid=computer.sidgo--查询视图--视图基于一张表创建，可以对视图实施增、删、改操作--视图基于多张表创建，不允许对视图实施。。。（在视图上创建instead of触发器）select * from view_stu_computerinsert into view_stu_computer values(4,'DDD',90)delete from view_stu_computer where sid=4create trigger insert_view_stu_computeron view_stu_computerinstead of insertas--从inserted表中获取插入的数据declare @id int,@name varchar(20),@marks floatselect @id=sid,@name=sname,@marks=marks from inserted--向基表中插入数据insert into stu values(@id,@name)insert into computer values(@id,@marks)gocreate trigger delete_view_stu_computeron view_stu_computerinstead of deleteas--从deleted表中获取正在删除的编号declare @id intselect @id=sid from deleted--从基表删除数据delete from computer where sid=@iddelete from stu where sid=@idgo
