数据库 触发器

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

select*from student
create trigger student_insert --创建触发器
on student --指定触发器所在的表
for Insert --当执行insert操作的时候自动执行触发器 after也一样
as
update student set sbirthday=‘1999-9-9‘ where sno=‘105‘
go

insert into Student values(‘203‘,‘约里克‘,‘男‘,‘1992-06-19‘,95033)



--instead of 替换
create trigger student_Delete
on student
instead of delete --替换某个操作，用触发器的过程来替换删除的操作，不再能够删除student表
as
update student set sbirthday=‘2015-4-29‘where sno=‘105‘
go

delete from student



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


for/after :先执行操作，再执行触发器
instead of:直接替换操作


--触发器中的临时表:deleted,inserted
create trigger Course_Delete
on course
instead of delete
as
    select*from deleted
go
delete from course where Cno=‘3-105‘
--deleted是一个临时表，里面存着你要删除的那些数据
drop trigger Course_Delete

--一次性删除course表数据，使用触发器替换删除操作，先删除外键表相关数据，再删除course

alter 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-105‘
select*from Score
select*from course


--删除事先备份数据到备份表之后再删除
create table scorebak
(
ids int identity(1,1) primary key,
sno char(3),
cno char(5),
degree decimal(4,1),
datetimes datetime
)
alter trigger score_delete
on score
instead of delete
as
    declare @count int
    select @count=count(*)from deleted
    
    declare @i int
    set @i=0
    while @i<@count --循环遍历delete临时表的数据，然后转移到备份表中
    begin
    declare @sno varchar(20)
    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())
    delete from score where sno =@sno and @cno=@cno
    set @i=@i+1
    end
go

delete from score where Sno=109
select*from scorebak
drop table scorebak