SQL Server学习之路(十七)下

2022-01-13 00:42:22

1.创建insert触发器

--创建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

--测试触发器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;

执行上面的语句后，结果如下图所示：

SQL Server学习之路(十七)下

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

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum

after insert
as
begin

    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)
--raiserror 是用于抛出一个错误
rollback transaction
end

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

结果如下：


SQL Server学习之路(十七)下

 2.创建delete触发器

　　用户执行delete操作，就会激活delete触发器，从而控制用户能够从数据库中删除数据记录，触发delete触发器后，用户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

--创建delete触发器
create trigger trig_delete
on student 

after delete
as
begin

    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age
   from deleted
end;

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

结果如下：

SQL Server学习之路(十七)下

 3.创建UPDATE触发器

　　update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。

--创建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

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

SQL Server学习之路(十七)下

 4.创建替代触发器

　　与前面介绍的三种after触发器不同，SqlServer服务器在执行after触发器的sql代码后，先建立临时的inserted表和deleted表，然后执行代码中对数据库操作，最后才激活触发器中的代码。而对于替代(instead of)触发器,SqlServer服务器在执行触发instead of 触发器的代码时，先建立临时的inserted表和deleted表，然后直接触发instead of触发器，而拒绝执行用户输入的DML操作语句。

--创建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

创建完成，执行一条insert语句触发触发器trig_insteadOf


SQL Server学习之路(十七)下5.嵌套触发器介绍

　如果一个触发器在执行操作时调用了另外一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

 

　　嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。如果嵌套的次数超过限制，那么该触发器将被终止，并回滚整个事务，使用嵌套触发器需要注意以下几点：

  • 默认情况下，嵌套触发器配置选项是开启的。
  • 在同一个触发器事务中，一个嵌套触发器不能被触发两次。
  • 由于触发器是一个事务，如果在一系列嵌套触发器的任意层次中发生错误，则整个事物都将取消，而且所有数据回滚。

嵌套是用来保持整个数据库的完整性的重要功能，但有时可能需要禁用嵌套，如果禁用了嵌套，那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下，需要禁用嵌套触发器：

  • 嵌套触发要求复杂而有理论的设计，级联修改可能会修改用户不想涉及的数据。
  • 在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器，尽管这时数据库提供很强的保护功能，但如果以特定的顺序更新表，就会产生问题。

使用下列语句禁用嵌套和再次启用嵌套：

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

6.递归触发器

　　触发器的递归是指一个触发器从其内部再一次激活该触发器，例如update操作激活的触发器内部还有一条数据表的更新语句，那么这个更新语句就有可能激活这个触发器本身，当然，这种递归的触发器内部还会有判断语句，只有一定情况下才会执行那个T_SQL语句，否则就成为无线调用的死循环了。

SqlServer中的递归触发器包括两种：直接递归和间接递归。

  • 直接递归：触发器被触发后并执行一个操作，而该操作又使用一个触发器再次被触发。
  • 间接递归：触发器被触发并执行一个操作，而该操作又使另一个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。

默认情况下，递归触发器选项是禁用的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令一样，所有数据都将回滚。

我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。

  • 间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1...
  • 直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1... 

设置直接递归：

默认情况下是禁止直接递归的，要设置为允许有两种方法：

  • T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；
  • EM：数据库上点右键->属性->选项。 

回到顶部

六:管理触发器　

1.查看触发器

(1).查看数据库中所有的触发器

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

sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。

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

use 数据库名go

exec sp_helptext '触发器名称'

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

 除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的属性

　　sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

use 数据库名
go
exec sp_helptrigger tableName

2.禁用启用触发器

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

　　如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

　　如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

3修改触发器

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

4.删除触发器

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

出处：http://www.cnblogs.com/selene/

另外更新了几本Oracle和Mysql的电子书到百度云盘了，有需要的可以点击菜单中的学习资料—>电子书即可看到下载地址，以后有好的电子书也会不定时更新进去，欢迎大家关注~



上一篇：云监控一键报警，为您的云上资源加一道快速保障


下一篇：step by step，实现自定义监控脚本