欢迎光临
个人知识库,ERP、IT知识分享和应用

sql server 触发器(trigger)

什么是触发器?

  • 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
  • 触发器通常用于强制业务规则
  • 触发器是一种高级约束,可以定义比用CHECK 约束更为复杂的约束 :可执行复杂的SQL语句(if/while/case)、可引用其它表中的列
  • 触发器定义在特定的表上,与表相关。
  •  自动触发执行
  •  不能直接调用
  •  是一个事务(可回滚)

使用触发器的好处

触发器可通过数据库中的相关表实现级联更改,不过,通过级联引用完整性约束可以更有效地执行这些更改。
触发器可以强制比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同,触发器可以引用
其它表中的列。例如,触发器可以使用另一个表中的 SELECT 比较插入或更新的数据,以及执行其它操作,
如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态,并根据其差异采取对策。
一个表中的多个同类触发器(INSERT、UPDATE 或 DELETE)允许采取多个不同的对策以响应同一个修改语句。

触发器的类型

  • DELETE 触发器
  • INSERT 触发器
  • UPDATE 触发器

after(For) 与 Instead of 的区别

after(for) 指在sql语句执行完成后再触发,sql语句会正常执行

instead of 在sql语句执行前触发,sql语句不会正常执行,比如要执行insert操作,需要在instead of 中写insert语句,不然你会发现,插入语句不管用了


创建触发器

创建触发器的语法

create trigger [触发器名称]
on [表名]
[WITH ENCRYPTION]
[for | after | instead of] [insert,update,delete]  -- for/after/instead of  insert/update/delete 
as
    <-- 代码块 -->

go

说明:
1 tr_name :触发器名称
2 on table/view :触发器所作用的表。一个触发器只能作用于一个表
3 for 和after :同义
4 after 与instead of :sql 2000新增项目afrer 与instead of 的区别
After
在触发事件发生以后才被激活,只可以建立在表上
Instead of
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
5 insert、update、delete:激活触发器的三种操作,可以同时执行,也可选其一
6 if update (col_name):表明所作的操作对指定列是否有影响,有影响,则激活触发器。此外,因为delete 操作只对行有影响,
所以如果使用delete操作就不能用这条语句了(虽然使用也不出错,但是不能激活触发器,没意义)。
7 触发器执行时用到的两个特殊表:deleted ,inserted
deleted 和inserted 可以说是一种特殊的临时表,是在进行激活触发器时由系统自动生成的,其结构与触发器作用的表结构是一
样的,只是存放 的数据有差异。

deleted 与inserted 数据的差异
Inserted
存放进行insert和update 操作后的数据
Deleted
存放进行delete 和update操作前的数据

Inserted表和Deleted表。此二表仅仅在触发器运行时存在。你可以使用该两个表来精确地确定触发触发器的动作对数据表所做的修改。

注意:update 操作相当于先进行delete 再进行insert ,所以在进行update操作时,修改前的数据拷贝一条到deleted 表中,修改后

的数据在存到触发器作用的表的同时,也同时生成一条拷贝到insered表中

set nocount on 
表示关闭受影响行数,如果不关闭的话,insert是有受影响行数的,会发生触发器中有一个受影响行数,真正的sql语句哪里又有个受影响行数
受影响行数关闭后,记得在触发器最后打开受影响行数

查看触发器

select * from sysobjects where xtype = 'TR' -- 查看数据库中所有的触发器
exec sp_helptext '触发器名称' -- 查看触发器代码

也可以使用可视化工具查看,在数据库-表-触发器中

修改触发器

修改触发器使用alter 关键字,直接将create 换成 alter就行

alter trigger [触发器名称]
on [表名]
[for | instead of | after] [insert,update,delete]
as
    <-- 代码块 -->
go

删除触发器

删除触发器使用 drop 关键字

drop trigger [触发器名称]

实例:

有一个表aaa,3个字段为:Id为自增

Id(自增)  No(数值型字段)    Name(姓名)
1         1        张名
2         2        李华
3         4        周青
—————————————-

if object_id('aaa') is not null 
drop table aaa
go
create table aaa(
Id int identity(1,1),
No int null,
Name varchar(20))
go
create trigger aaa_ins
on aaa
after insert
as
   update aaa set No=i.Id
        from inserted i 
        where aaa.Id=i.Id
go
insert into aaa(Name) values('a');
select * from aaa;

直接增加

create table aaa
( Id int identity(1,1),
  No int default(isnull(scope_identity(),0)+1),
  Name varchar(20)
)

insert into aaa(Name) values('a')

select * from aaa
未经允许不得转载:Blog.XiaoMing.Xyz » sql server 触发器(trigger)
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址