-----------触发器
--触发器本质上还是一个存储过程
--只不过不是通过exec来调用执行,而是通过增删改数据库的操作来引发执行
select *from student
select *from score
alter trigger TR_student_Delete
on student
--for Delete--因为先执行里面的在执行外面的 先删除后操作
instead of delete--当删除时先引发触发器,用触发器里的操作替换本来的操作
as
delete from score where sno=108
--insert into student values('108','曾鹏','男','1997-7-5','95033')
delete from student where sno=108
go
delete from student where sno=108
create trigger dongtaichufa
on teacher
instead of delete--删除的时候触发触发器
as
begin
declare @tno varchar(20)
set @tno=(select tno from deleted)
update teacher set tname='张三'where tno=@tno
end
go
delete from teacher where tno='825'
select*from teacher
create trigger TR_teacher_insert
on teacher
for insert
as
begin
declare @tno varchar(20)
set @tno=(select tno from inserted)
delete @sex varchar(20)
set @aex=(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','教授','计算机系')
alter table teacher disable trigger all--禁用数据表的所有触发器
alter table teacher enable trigger all--开启数据表的所有触发器