触发器

--什么触发器？
是通过事件来触发的；
有两种类型的触发器：DML触发器 和 DDL触发器；
DML触发器：由insert、update、delete事件触发；
DDL触发器：由DDL命令触发（create, alter, drop）
DML触发器分为：after触发器 和 instead of触发器；
--为什么要使用触发器?
5种约束：主键primary key、 外键foreign key、 唯一unique、check约束、默认值default
使用触发器可实现比上述5种约束更复杂的约束；
--如何使用使用触发器？
有两个特殊的表：inserted表和deleted表；
这两个表是有系统管理的，是存储在内存中的，不是存储在数据库中的，是只读的；
这两个表式动态驻留在内存中的，当触发器工作完成，这两个表就会被删除；
这两个表的结构总是与被该触发器作用的表有相同的结构；
--（1）创建DML触发器
--语法
create trigger 触发器名 on 表名 
for insert, update, delete
as
 .....
--eg1：为Student表创建一个触发器，理解insterted表与deleted表的作用！
--判断触发器是否存在，若存在，则删先
if object_id('tri_InsStudent') is not null
 drop trigger tri_InsStudent
go
--创建触发器
create trigger tri_InsStudent on Student 
 for insert, delete, update
as
 print '触发器被触发了！'
 select * from inserted
 select * from deleted
--触发该触发器的测试语句
insert into Student values('95005',  '王桃群',  '女',  20,  'cs') 
delete from Student where Sno = '95005'
update Student set Sname = '张三' where Sno = '95005'
--eg2：为SC表创建一个触发器，要求：当向SC表插入一条选课记录时，平均成绩表中，该生的平均成绩自动更新
--Step1: 创建平均成绩表先
create table avgTable(  
 Sno          char(5),  --学号
 avgGrade int             --平均成绩
)
--Step2: 将选课学生的学号和平均成绩插入到avgTable表中
insert into avgTable
select Sno, avg(Grade) from SC group by Sno
--Step3: 咱们查看下平均成绩表avgTable中的数据
select * from avgTable
--Step4: 在SC表上创建一个触发器
create trigger tri_InsertSC on SC for insert
as
  declare @Sno char(5)
  select @Sno = Sno from inserted
  if not exists(select * from inserted, avgGrade  where avgGrade.Sno = @Sno) 
  insert into avgGrade select Sno, grade from inserted
 else    
  update avgGrade set avgGrade = (select avg(Grade) from SC group by Sno having Sno = @Sno)
  where avgGrade.Sno = @Sno
--Step5: 触发器创建好后，咱们在SC表中插入一条选课记录测试一下
insert into SC values('95003', '2', 80)
--Step6: 观察平均成绩表中的数据是否自动更新了
select * from SC
select * from avgTable
--Step7: 思考题：请在SC表上创建一个触发器，当删除SC表中的选课记录时，要求平均成绩表中平均成绩自动更新；
--Step8: 思考题：请在SC表上创建一个触发器，当更新SC表中的选课成绩时，要求平均成绩表中平均成绩自动更新；
--（2）创建DDL触发器
--eg1：在数据库上创建一个触发器，禁止用户在该数据库上删除表、创建视图
create trigger safety on DATABASE for drop_table, create_view
as
 print '不允许删除表，也不允许创建视图'
 rollback
--测试该触发器
create view v1
as
 select * from Student where Sage > 20
--eg2：在服务器上创建一个触发器，禁止创建登陆用户、修改用户登陆、删除用户登陆
create trigger tri_OnServer on all server for DDL_Login_events
as
 print '登陆操作失败！'
 rollback
--测试在触发器
create login wtq with password = '123'