--触发器:就是一种特殊的存储过程
--特殊的地方的地方是对数据库表的操作来引发
--存储过程时通过人为的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