第七章 触发器

触发器：

定义：触发器是在对表或视图进行插入，更新或删除操作时自动执行的存储过程

作用及用处：

    * 触发器通常用于强制业务规则

   * 触发器是一种高级约束，可以定义比用check约束更为复杂的约束{可执行复杂的sql语句（if、while、case）；可引用其它表中的列}

  *触发器定义在特定的表或视图上，与表或视图相关

  *自动出发执行

 *不能直接调用

 *是一个事物（可回滚）

类型：

1. after触发器（只能定义在表上）

        after触发器又包括：insert触发器 update触发器 delete触发器

2.instead of触发器（可以定义在表或视图上）

创建触发器语法：

  --创建触发器的语法
--create trigger 触发器名称
--on 需要监测的表名
--[with encryption]加密
--for/instead of(触发器的类型 for是after触发器)insert delete update
--as
--sql语句

go

删除语法：

if exists (select * from sys.triggers where name ='触发器名称')
drop trigger '触发器名称'
go

例：

--禁止添加性别为男的同学

create trigger tr_insert
on stuinfo
for insert
as
 declare @sex varchar(2)
 select  @sex=stusex from inserted--这里使用inserted表
 if @sex='男'
 rollback tran
 go
 insert into StuInfo values('孙悟空','女')
 
 select * from StuInfo
 go

 --禁止删除性别为女的同学
if exists (select * from sys.triggers where name ='tr_delete')
drop trigger tr_delete
go
create trigger tr_delete
on stuinfo
for delete
as
declare @sex varchar(2)
select @sex= stusex from deleted

begin
if @sex ='女'
 rollback tran
end

go
delete from StuInfo where stusex='女'

 select * from StuInfo
go

--禁止修改某一列（使用update语句）

if exists (select * from sys.triggers where name ='tr_update')
drop trigger tr_update
go
create trigger tr_update
on stuinfo
for update
as
if update(stusex)--update(列名)
begin
print '性别不允许修改！！！'
rollback tran--回滚
end
go
update StuInfo set stusex='女'

select * from StuInfo
go

--替代触发器
if exists (select * from sys.triggers where name ='tr_instead')
drop trigger tr_instead
go
create trigger tr_instead
on StuInfo
instead of delete
as
  declare @id int
  select @id=stuid from deleted
  delete from StuMarks where stuid=@id
  delete from StuInfo where stuid=@id
go

delete from StuInfo where stuid=2
select * from StuInfo
go

 

--定义在视图上
1.建立视图

if exists (select * from sys.views where name ='v_delete')
drop view v_delete
go
create view v_delete
as
 select StuInfo.stuid,stuname,subject,score from StuInfo,StuMarks where StuInfo.stuid=StuMarks.stuid
go
select *from v_delete
go

2.创建触发器
if exists (select * from sys.triggers where name ='tr_instead')
drop trigger tr_instead
go
create trigger tr_instead
on v_delete
instead of delete
as
  declare @id int
  select @id=stuid from v_delete
  delete from StuMarks where stuid=@id
  delete from StuInfo where stuid=@id
go

delete from StuMarks where stuid=2
select * from StuMarks
go