SqlServer基础之(触发器)

它是与表事件相关的特殊的存储过程,触发器和存储过程的区别,而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户,触发器和存储过程的区别,而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户,触发器和存储过程的区别

5:创设触发器

 创建触发器的语法: 

金沙国际唯一官网网址 1😉

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

金沙国际唯一官网网址 2😉

 图谋测试数据:

金沙国际唯一官网网址 3😉

--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)

金沙国际唯一官网网址 4😉

1.创建insert触发器

金沙国际唯一官网网址 5😉

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

金沙国际唯一官网网址 6😉

金沙国际唯一官网网址 7😉

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中,更新stuCount
--因为是后触发器,所以先插入数据后,才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;    
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);            
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);                
select stuCount 学生总人数 from student_sum;

金沙国际唯一官网网址 8😉

施行上边包车型地铁言语后,结果如下图所示:

金沙国际唯一官网网址 9

 既然定义了学生总的数量表student_sum表是向student表中插入数据后才计算学生总的数量的,所以学生总量表应该禁止用户向里面插入数据

金沙国际唯一官网网址 10😉

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录,操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end 

金沙国际唯一官网网址 11😉

--触发触发器insert_forbidden
insert student_sum (stuCount) values(5);

结果如下:

金沙国际唯一官网网址 12

 2.创建delete触发器

  用户实行delete操作,就能激活delete触发器,从而调整用户能够从数据库中除去数据记录,触发delete触发器后,用户删除的记录会被增添到deleted表中,原来表的附和记录被剔除,所以在deleted表中查阅删除的记录。

金沙国际唯一官网网址 13😉

--创建delete触发器
create trigger trig_delete
on student 
after delete
as
begin
    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age
    from deleted
end;

金沙国际唯一官网网址 14😉

--执行一一条delete语句触发trig_delete触发器
delete from student where stu_id=1;

结果如下:

金沙国际唯一官网网址 15

 3.创建UPDATE触发器

  update触发器是当用户在钦赐表上实施update语句时被调用被调用,那体系型的触发器用来约束用户对数据的改造。update触发器可以进行二种操作:更新前的笔录存款和储蓄在deleted表中,更新后的记录存款和储蓄在inserted表中。

金沙国际唯一官网网址 16😉

--创建update触发器
create trigger trig_update
on student
after update
as
begin
    declare @stuCount int;
    select @stuCount=count(*) from student;
    update student_sum set stuCount =@stuCount;
    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted
    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted
end

金沙国际唯一官网网址 17😉

--创建完成,执行一条update语句触发trig_update触发器
update student set stu_name='张飞' where stu_id=2;

金沙国际唯一官网网址 18

金沙国际唯一官网网址, 四.创制代替触发器

  与前方介绍的三种after触发器不相同,SqlServer服务器在实施after触发器的sql代码后,先成立一时半刻的inserted表和deleted表,然后实行代码中对数据库操作,最后才激活触发器中的代码。而对于代表(instead
of**
)触发器,SqlServer服务器在推行触发instead
of
触发器的代码时,先创造
临时的inserted表和deleted表,然后径直触发instead
of触发器,而拒绝施行用户输入的DML操作语句。**

金沙国际唯一官网网址 19😉

--创建instead of 触发器 
create trigger trig_insteadOf
on student 
instead of insert
as 
begin
    declare @stuAge int;
    select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
    select '插入年龄错误' as '失败原因'
end

金沙国际唯一官网网址 20😉

开创完毕,试行一条insert语句触发触发器trig_insteadOf

金沙国际唯一官网网址 21

伍.嵌套触发器介绍

 假诺1个触发器在实行操作时调用了其余1个触发器,而这么些触发器又接着调用了下三个触发器,那么就产生了嵌套触发器。嵌套触发器在安装时就被启用,不过足以行使系统存款和储蓄进程sp_configure禁止使用和再一次启用嵌套触发器。

 

  嵌套触发器不必然要产生三个环,它能够 T一->T二->T3…那样直白触发下去,最多允许嵌套 3二 层。假使嵌套的次数超过限制,那么该触发器将被终止,并回滚整个业务,使用嵌套触发器需求小心以下几点:

  • 私下认可意况下,嵌套触发器配置选项是翻开的。
  • 在同三个触发器事务中,3个嵌套触发器不可能被触发一次。
  • 由于触发器是二个作业,假诺在一文山会海嵌套触发器的即兴档案的次序中生出错误,则整个事物都将打消,而且具备数据回滚。

嵌套是用来维持总体数据库的完整性的重中之重功用,但神蹟可能须求禁止使用嵌套,固然禁止使用了嵌套,那么修改2个触发器的得以达成不会再接触该表上的其余触发器。在下述情状下,须求禁止使用嵌套触发器:

  • 嵌套触发须求复杂而有理论的统一图谋,级联修改可能会修改用户不想涉及的多少。
  • 在一文山会海嵌套触发器中的放4点的年华修改操作都会触发一些触发器,就算那时数据库提供很强的爱慕效用,但假若以一定的一壹更新表,就能够发出难点。

应用下列语句禁止使用嵌套和重新启用嵌套:

--禁用嵌套
exce sp_configure 'nested triggers',0;
--启用嵌套
exce sp_configure 'nested triggers',1;

6.递归触发器

  触发器的递归是指二个触发器从当中间再1遍激活该触发器,举例update操作激活的触发器内部还有一条数据表的立异语句,那么那么些创新语句就有不小可能率激活那么些触发器本人,当然,那种递归的触发器内部还会有咬定语句,唯有一定情状下才会施行那几个T_SQL语句,不然就产生有线调用的死循环了。

SqlServer中的递归触发器包罗二种:直接递归和直接递归。

  • 直接递归:触发器被触发后并试行贰个操作,而该操作又采纳3个触发器再度被触发。
  • 直接递归:触发器被触发并执行贰个操作,而该操作又使另一个表中的某部触发器被触发,第四个触发器使原始表得到更新,从而再度接触第2个触发器。

私下认可情形下,递归触发器选项是禁用的。递归触发器最八只好递归16层,倘诺递归中的第壹五个触发器激活了第3多少个触发器,则结果与公布的rollback命令同样,全部数据都将回滚。 

大家比方解释如下,假诺有表一、表2名称分别为 T一、T二,在 T一、T贰 上各自有触发器 G一、G2。

  • 直接递归:对 T一 操作从而触发 G一,G一 对 T2 操作从而触发 G二,G2 对 T1 操作从而再度触发 G一…
  • 一贯递归:对 T一 操作从而触发 G一,G1 对 T一 操作从而再度触发 G一… 

安装直接递归:

暗中认可境况下是明确命令禁止直接递归的,要设置为允许有三种艺术:

  • T-SQL:exec sp_dboption ‘dbName’, ‘recursive triggers’, true;
  • EM:数据库上点右键->属性->选项。 

归来顶部

  触发器与存款和储蓄进度的分别是运维形式的两样,触发器无法实行EXECUTE语句调用,而是在用户试行Transact-SQL语句时自动触发实施而存款和储蓄进度须要用户,应用程序恐怕触发器来展现地调用并进行。

肆:触发器的办事原理

触发器触发时:

  1. 系统自动在内部存款和储蓄器中成立deleted表或inserted表;
  2. 只读,不允许修改,触发器推行到位后,自动删除。

inserted表:

  1. 临时保存了插入或更新后的记录行;
  2. 能够从inserted表中检查插入的数据是不是满意专业要求;
  3. 假如不满意,则向用户发送报告错误音讯,并回滚插入操作。 

deleted表:

  1. 暂且保存了去除或更新前的记录行;
  2. 能够从deleted表中检查被删除的数码是还是不是知足工作须要;
  3. 假如不满意,则向用户告知错误消息,并回滚插入操作。

inserted表和deleted表对照: 

修改操作记录 inserted表 deleted表
增加(insert)记录 存放新增的记录 …………
删除(deleted)记录 ………….. 存放被删除的记录
修改(update)记录 存放更新后的记录 存放更新前的记录

 

 

 

金沙国际唯一官网网址 22

二:触发器的作用

 触发器的珍视意义就是其能够达成由主键和外键所不能够担保的盘根错节参照完整性和多少的1致性,它可以对数据库中的相关表实行级联修改,提升比CHECK约束更千头万绪的的数据完整性,并自定义错误新闻。触发器的严重性功能首要有以下接个地点:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中颇具备关的表,自动触发别的与之互为表里的操作
  3. 钉住变化,裁撤或回滚违规操作,幸免不法修改数据
  4. 再次来到自定义的谬误新闻,约束不可能回来音讯,而触发器能够
  5. 触发器能够调用更加多的贮存进程

再次回到顶部

④:触发器的办事原理

触发器触发时:

  1. 系统活动在内部存款和储蓄器中创设deleted表或inserted表;
  2. 只读,不容许修改,触发器推行到位后,自动删除。

inserted表:

  1. 一时保存了插入或更新后的记录行;
  2. 能够从inserted表中检查插入的数额是不是满意职业必要;
  3. 如若不满意,则向用户发送报告错误信息,并回滚插入操作。 

deleted表:

  1. 一时保存了去除或更新前的记录行;
  2. 能够从deleted表中检查被删除的数据是还是不是满意专门的学业须要;
  3. 固然不满意,则向用户告知错误音讯,并回滚插入操作。

inserted表和deleted表对照: 

修改操作记录 inserted表 deleted表
增加(insert)记录 存放新增的记录 …………
删除(deleted)记录 ………….. 存放被删除的记录
修改(update)记录 存放更新后的记录 存放更新前的记录

 

 

 

金沙国际唯一官网网址 22

陆:管理触发器 

1.查看触发器

(壹).查看数据库中全部的触发器

--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

sysobjects 保存着数据库的目的,在那之中 xtype 为 T中华V 的记录即为触发器对象。在 name 一列,大家得以观望触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名
go
exec sp_helptext '触发器名称'

 将会以表的样式彰显触发器内容。 

 除了触发器外,sp_helptext 还足以显得 规则、暗许值、未加密的仓库储存进度、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的品质

  sp_helptrigger 有七个参数:第二个参数为表名;第三个为触发器类型,为 char(陆) 类型,能够是 INSERT、UPDATE、DELETE,借使轻易则显得钦点表中全部种类触发器的习性。

use 数据库名
go
exec sp_helptrigger tableName

二.禁止使用启用触发器

  禁止使用:alter table 表名 disable trigger 触发器名称
  启用:alter table 表名 enable trigger 触发器名称

  假如有两个触发器,则相继触发器名称之间用英文逗号隔离。

  假使把“触发器名称”换来“ALL”,则意味禁止使用或启用该表的百分百触发器。

三修改触发器

--修改触发器语法
ALTER TRIGGER  trigger_name 
     ON  table_name 
     [ WITH ENCRYPTION ] 
     FOR {[DELETE][,][INSERT][,][UPDATE]}
     AS
       sql_statement;

四.刨除触发器

 --语法格式:
      DROP  TRIGGER   { trigger } [ ,...n ]
参数:
 trigger: 要删除的触发器名称
 n:表示可以删除多个触发器的占位符       

概念:

概念:

贰:触发器的功能

 触发器的基本点职能正是其能够落实由主键和外键所不能够保险的盘根错节参照完整性和数码的一致性,它能够对数据库中的相关表打开级联修改,提升比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要功用重大有以下接个方面:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中有所相关的表,自动触发别的与之相关的操作
  3. 盯住变化,撤除或回滚违规操作,幸免违法修改数据
  4. 回到自定义的失实新闻,约束不可能再次回到新闻,而触发器能够
  5. 触发器可以调用更加多的存款和储蓄进程

1:触发器的优点

 一.触发器是自动的。当对表中的数额做了别的更换之后立刻被激活。

 二.触发器可以通过数据库中的相关表张开层叠修改。

 三.触发器能够强制限制。那个限制比用CHECK约束所定义的更复杂。与CHECK约束差别的是,触发器能够引用别的表中的列。

归来顶部

触发器和积存进度的界别:

  触发器与仓库储存进度的界别是运市价势的不等,触发器无法试行EXECUTE语句调用,而是在用户实施Transact-SQL语句时自动触发实践而存款和储蓄过程要求用户,应用程序或然触发器来体现地调用并实践。

开卷目录

  触发器(trigger)是SQL server 提供给技术员和数量分析员来保证数据完整性的一种办法,它是与表事件相关的特种的贮存进度,它的推行不是由程序调用,也不是手工业运营,而是由事件来触发,当对三个表实行操作( insert,delete, update)时就能激活它执行。触发器平常用来加强数据的完整性约束和作业规则等。 触发器可以从 DBA_TRIGGERS ,USER_TLX570IGGEPRADOS 数据字典中查到。

  触发器(trigger)是SQL server 提须要技师和数码分析员来保障数据完整性的1种艺术,它是与表事件相关的独树一帜的仓库储存过程,它的实施不是由程序调用,也不是手工业运营,而是由事件来触发,当对3个表张开操作( insert,delete, update)时就能够激活它推行。触发器平常用来提升数据的完整性约束和事务规则等。 触发器能够从 DBA_TRIGGERS ,USER_TENCOREIGGELX570S 数据字典中查到。