T-SQL之触发器（下）

前触发器

create view v1
as
select student.sno,ssex,cno,grade
from student,sc
where student.sno=sc.sno

=====================
delete from v1 where ssex='W' --不能删除，因为：

视图或函数 'v1' 不可更新，因为修改会影响多个基表。

这时就该前触发器上场了

create trigger mytri2
on v1
instead of delete
 as
delete from sc where sno in
(select sno from deleted)
delete from student where sno in
(select sno from deleted)

不过只能把视图中符合条件的删除，sc，student表中都不能删除。

创建触发器，禁止删除或更改某表

create trigger mytri
on database
for drop_table,alter_table
as
begin
 print '在当前数据库下禁止删除、修改任何表'
 rollback transaction
end