SQL触发器

--触发器:就是一种特殊的存储过程
--特殊的地方的地方是对数据库表的操作来引发
--存储过程时通过人为的exec来执行

create table Student
(
  Sno varchar(50)primary key not null,        --学号（主码）
  Sname varchar(50) not null,        --学生姓名
  Ssex varchar(50)not null,        --学生性别
  Sbirthday datetime,     --学生出生年月
  Class varchar(50)       --学生所在班级
)
go
--truncate table Student
insert into Student values(‘108‘,‘曾华‘,‘男‘,‘1977-09-01‘,‘95033‘)
insert into Student values(‘105‘,‘匡明‘,‘男‘,‘1975-10-02‘,‘95031‘)
insert into student values(‘107‘,‘王丽‘,‘女‘,‘1976-01-23‘,‘95033‘)
insert into student values(‘101‘,‘李军‘,‘男‘,‘1976-02-20‘,‘95033‘)
insert into student values(‘109‘,‘王芳‘,‘女‘,‘1975-02-10‘,‘95031‘)
insert into student values(‘103‘,‘陆君‘,‘男‘,‘1974-06-03‘,‘95031‘)
create table course
(
 Cno varchar(50)primary key not null,    --课程号（主码）
 Cname varchar(50) not null,    --课程名称
 Tno varchar(50)not null,
 foreign key (Tno)
 references teacher(Tno)      --教工编号（外码）
)
--truncate table course
insert into course values(‘3-105‘,‘计算机导论‘,‘825‘)
insert into course values(‘3-245‘,‘操作系统‘,‘804‘)
insert into course values(‘6-166‘,‘数字电路‘,‘856‘)
insert into course values(‘9-888‘,‘高等数学‘,‘831‘)

create table Score
(
 Sno varchar(50)not null,    --学号（外码）
 Cno varchar(50)not null,    --课程号（外码）
 foreign key (Sno)
 references student(Sno) ,
 foreign key (cno)
 references course(Cno) , 
 Degree	Decimal(4,1)   --成绩
 primary key (Sno,Cno)
)
--truncate table Score
insert into Score values(‘103‘,‘3-245‘,	86)
insert into Score values(‘105‘,‘3-245‘,	75)
insert into Score values(‘109‘,‘3-245‘,	68)
insert into Score values(‘103‘,‘3-105‘,	92)
insert into Score values(‘105‘,‘3-105‘,	88)
insert into Score values(‘109‘,‘3-105‘,	76)
insert into Score values(‘101‘,‘3-105‘,	64)
insert into Score values(‘107‘,‘3-105‘,	91)
insert into Score values(‘108‘,‘3-105‘,	78)
insert into Score values(‘101‘,‘6-166‘,	85)
insert into Score values(‘107‘,‘6-166‘,	79)
insert into Score values(‘108‘,‘6-166‘,	81)
create table Teacher
(
  Tno varchar(50)primary key not null,        --教工编号（主码）
  Tname varchar(50) not null,        --教工姓名
  Ssex varchar(50)not null,        --教工性别
  Sbirthday datetime,     --教工出生年月
  Prof varchar(50)not null,
  Depart Varchar(50)       --教工所在部门
)
truncate table Teacher
insert into Teacher values(‘804‘,‘李诚‘,‘男‘,‘1958-12-02‘,‘副教授‘,‘计算机系‘)
insert into Teacher values(‘856‘,‘张旭‘,‘男‘,‘1969-03-12‘,‘讲师‘,	‘电子工程系‘)
insert into Teacher values(‘825‘,‘王萍‘,‘女‘,‘1972-05-05‘,‘助教‘,	‘计算机系‘)
insert into Teacher values(‘831‘,‘刘冰‘,‘女‘,‘1977-08-14‘,‘助教‘,	‘电子工程系‘)

---------------格式
--create trigger 触发器名
--on 表名
--(for/after)|(instead of) 动作(insert|updata|delete)
--as
--   存储过程内容
--go


--for/after: 先执行操作 在执行触发器
--instead of :直接替换操作
--drop trigger 表名  --删除触发器



select *from Student 
create trigger student_Insert              --创建触发器
on student                          --指定触发器所在表
for Insert            --先执行操作 在执行触发器
as
  update student set sbirthday=‘1999—9—9‘ where sno=‘101‘
go
insert into student values(‘203‘,‘皮特‘,‘男‘,‘1976-02-20‘,‘95033‘)
drop trigger student_Insert



--触发器中的临时表
--触发器中的临时表:deleted,   inserted ?

create trigger Course_Delete 
on course
instead of delete                     --替换了删除    
as
	select *from deleted           --deleted是一个临时表，里面存着你要删除的那些数据、
go
delete from course where cno=‘6-166‘    --执行删除cno=‘3-105‘替换了删除 从deleted临时表会显示你要删除的内容
select*from score
select*from course
drop trigger course_delete     --删除触发器



create trigger Course_Delete                     --删除你想要删除的数据      级联删除
on course
instead of delete
as
	delete from score where cno in (select cno from deleted)      --要删除主键内容 必须先删掉外键内容 才能删除主键内容
	delete from course where cno in (select cno from deleted)        
go
delete from course where cno=‘3-245‘
select*from score
select*from course
drop trigger course_delete


--删除前先备份数据到备份表之后再删除

 create table scorebak              --先创建备份表
(
	ids int identity(1,1) primary key ,
	sno varchar(50),
	cno varchar(50),
	degree decimal(18,2),
	datetimes datetime
)
create trigger Score_delete          --创建触发器
on score
instead of delete
as
	declare @count int     --定义变量
	select @count=count(*) from deleted  --计算deleted里面共有的数据   
	
	declare @i int    --  定义变量                    
	set @i=0       --赋值 定义0 下面的分页排除第0行 从第一页开始 一行一页
	while @i<@count  --循环遍历deleted临时表的数据，然后转移
	begin
		declare @sno varchar(20)   --定义三个变量来接收score里面的三个列
		declare @cno varchar(20)
		declare @degree decimal(4,1)
		
		select top 1 @sno=sno,@cno=cno,@degree=degree from deleted         -- 分页 这是组合键   不是组合键的话  只写主键的就可以
		where sno not in (select top (@i) sno from deleted)
		or cno not in (select top (@i) cno from deleted )
		
		insert into scorebak values(@sno,@cno,@degree,getdate())   --往新的表里添加从score删除的数据
		
		delete from score where sno=@sno and cno=@cno            --删除score里面的数据
 
		set @i=@i+1                --根据@i加1 一行一行的循环
	end
	
go 
delete from score where cno=‘3-105‘

 select*from score
 select*from scorebak