触发器(方便备份)
本质上还是一个存储过程,只不过不是通过exec来调用执行,而是通过增删改数据库的操作来执行(可以操作视图)
全部禁用触发器
alter table teacher disable trigger all
全部开启触发器
alter table teacher enable trigger all
create trigger TR_student_delete--默认触发器名
on student --在哪个表上操作
instead of delete --为了对表做什么而建的,‘for’是在执行完外部语句之后用,‘instead of’是直接将要执行的语句替换为执行as里面的语句
as
delete from score where sno =108
delete from student where Sno = 108
go
delete from student where Sno = 108
select *from student
108行被删掉
alter trigger TR_student_delete--默认触发器名
on student --在哪个表上操作
for delete
as
insert into student values(108,'曾鹏','男',1905-05-22,95033)
go
delete from student where Sno = 108
select *from student
*曾华变为了曾鹏
动态触发
create trigger dongtaichufa
on teacher
instead of delete
as
begin
declare @tno varchar(20)
set @tno = (select tno from deleted)—-选择执行语句中的tno所在@tno
update teacher set tname='张三'where tno = @tno
end
go
delete from teacher where Tno='831'
deleted存储删除后的信息,inserted存放更改后的信息。
执行delete语句时的tno=触发器里面的tno,而不是@tno
create trigger TR_teacher_insert
on teacher
for insert
as
begin
declare @tno varchar(20)
set @tno=(select tno from inserted)
---delete from teacher where tno=@tno
declare @sex varchar(10)
set @sex =(select tsex from teacher where tno=@tno)
if(@sex='男')
update teacher set tsex='女'where tno =@tno
else
update teacher set tsex='男'where tno =@tno
end
go
insert into teacher values (800,'王五','男',1990-09-09,'教授','计算机系')