您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 2008从入门到精通,触发器入门

2008从入门到精通,触发器入门

发布时间:2019-10-14 02:43编辑:1010cc安卓版浏览(55)

    1.1.DDL触发器

    当服务器或数据库中发生数据定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。如果要执行以下操作,可以使用DDL触发器:

    • 防止对数据库架构进行更改
    • 希望数据库中发生某些情况以响应数据库架构中的更改
    • 要记录数据库架构中的更改或事件

            在SQL SERVER 2008中,DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中,我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后,它们也就会被自动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的记录。比如你插入一条数据,那么就会把这条记录插入到INSERTED表:DELETED表用于存放你在操作  insert、update、delete语句前,你创建触发器表中数据库。比如你原来的表中有三条数据,那么他也有三条数据。也就是说,我们可以使用这两个临时的驻留内存的表,测试某些数据修改的效果及设置触发器操作的条件。

      触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。

    六:管理触发器 

    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修改触发器

    图片 1;)

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

    图片 2;)

    4.删除触发器

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

    原文转自:

    1.3.1.创建DML触发器

            (1)DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。         (2)DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。         (3)登陆触发器:是指当用户登录SQL SERVER实例建立会话时触发。

    一:触发器的优点

     1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

     2.触发器可以通过数据库中的相关表进行层叠修改。

     3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。

    四:触发器的工作原理

    触发器触发时:

    1. 系统自动在内存中创建deleted表或inserted表;
    2. 只读,不允许修改,触发器执行完成后,自动删除。

    inserted表:

    1. 临时保存了插入或更新后的记录行;
    2. 可以从inserted表中检查插入的数据是否满足业务需求;
    3. 如果不满足,则向用户发送报告错误消息,并回滚插入操作。 

    deleted表:

    1. 临时保存了删除或更新前的记录行;
    2. 可以从deleted表中检查被删除的数据是否满足业务需求;
    3. 如果不满足,则向用户报告错误消息,并回滚插入操作。

    inserted表和deleted表对照: 

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

     

     

     

    图片 3

    回到顶部

    1.2.DML触发器

    当数据库服务器中发生数据操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,如果检测到错误,则整个事务回滚。DML触发器在一下方面非常有用:

    • 可实现数据库相关表之间的级联更改
    • 可以防止恶意或错误的DML语句事件,并强制执行比CHECK约束更为复杂的其他限制
    • 可以评估数据修改前后表的状态,并根据该差异采取措施

    一个表中的多个同类DML触发器,允许用多个不同的操作来响应同一个修改语句
    SQL Server 2008为每个触发器创建了2个特殊的表:INSERTED表和DELETED表。这是两个逻辑表,由系统来创建和维护,用户不能对他们进行修改。它们存放在内存中,而不是在数据库中,并且结构与被DML触发器作用的表的结构相同。
    INSERTED表中存放了由执行INSERTUPDATE语句而插入的所有行,在执行INSERTUPDATE语句时,新的行将同时被插入到触发器作用的表和INSERTED表中。INSERTED表中的行是触发器作用的表中行的副本。
    DELETED表中存放了由执行DELETEUPDATE语句而删除的所有行,在执行DELETEUPDATE语句时,被删除的行将由触发器作用的表中被移动到DELETED表,两个表中不会有重复行。

            INSERTED和DELETED

    五:创建触发器

     创建触发器的语法: 

    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指定触发器的类型
    

     准备测试数据:

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

    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;
    

    执行上面的语句后,结果如下图所示:

    图片 4

     既然定义了学生总数表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);
    

    结果如下:

    图片 5

     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;
    

    结果如下:

    图片 6

     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;
    

    图片 7

     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

    图片 8

    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.3.1.3.UPDATE触发器

    当针对目标数据库运行UPDATE语句时就会激活UPDATE触发器。对UPDATE触发器来说,临时表INSERTEDDELETED依然有用。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来确定是否更新了多行和如何执行触发器动作。
    Student表的数据如图所示
    图片 9
    Class_student表的数据如图所示
    图片 10
    示例5:当student表中的stu_no字段更新时,同步更新class_student表中的stu_no字段
    执行下列语句新建触发器update_stu_no_single

    CREATE TRIGGER update_stu_no_single
    ON student
    FOR UPDATE
    AS
    IF UPDATE(stu_no)
    BEGIN
    UPDATE class_student
    SET stu_no=(SELECT stu_no FROM inserted)
    WHERE stu_no=(SELECT stu_no FROM deleted)
    END
    GO
    

    验证update_stu_no_single触发器是否正确,在Student表中执行下列语句,将student表中stu_no为“20180101”的学生的stu_no改成00000000

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no='20180101'
    

    执行成功后,update_stu_no_single触发器被激活,class_student表的数据如图所示
    图片 11

    注:update_stu_no_single触发器只能对单行记录的UPDATE操作起效,如果批量UPDATE stu_no,执行语句时会提示子查询返回的值不止1个。下面的示例6将提供批量UPDATE stu_no的触发器

    示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也同步批量更新
    首先将student表和class_student表的数据修改成原来的样子,并且删除update_stu_no_single触发器
    Student表的数据如图所示
    图片 12
    Class_student表的数据如图所示
    图片 13
    执行下列语句新建触发器update_stu_no_batch

    CREATE TRIGGER update_stu_no_batch
    ON student
    FOR UPDATE
    AS
    DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
    DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM inserted
    OPEN stu_cursor_insert
    DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM deleted
    OPEN stu_cursor_delete
    FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
    FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
    WHILE @@FETCH_STATUS=0
    BEGIN
    UPDATE class_student
    SET stu_no=@stu_no_insert
    WHERE stu_no=@stu_no_delete
    FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
    FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
    END
    CLOSE stu_cursor_insert
    CLOSE stu_cursor_delete
    DEALLOCATE stu_cursor_insert
    DEALLOCATE stu_cursor_delete
    GO
    

    验证update_stu_no_batch触发器的准确性,对student表执行下列语句,实现批量修改操作

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no LIKE '201801%'
    GO
    

    Student表的数据如图所示
    图片 14
    Class_student表的数据如图所示
    图片 15
    我们再来验证update_stu_no_batch触发器对更新单行stu_no数据是否有效。将student表class_student表的数据改回原来的样子,然后执行下列语句

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no='20180101'
    

    Class_student表的数据如图所示
    图片 16

    注:在将表数据改成原来的样子时,直接在编辑前200行中操作或者用T-SQL语句操作,对student表数据操作,不成功的话要考虑受键和约束的影响,对class_student表数据操作,不成功的话要考虑受触发器影响。

     

    二:触发器的作用

     触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要作用主要有以下接个方面:

    1. 强制数据库间的引用完整性
    2. 级联修改数据库中所有相关的表,自动触发其它与之相关的操作
    3. 跟踪变化,撤销或回滚违法操作,防止非法修改数据
    4. 返回自定义的错误消息,约束无法返回信息,而触发器可以
    5. 触发器可以调用更多的存储过程

    阅读目录

    1.3.4.1.递归触发器

    触发器被激活,更改了表中数据,这种更改又激活了它自己,这种触发器被称为递归触发器。数据库创建时默认递归触发器禁用。但可以使用ALTER DATABASE选项来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用状态,如果嵌套触发器禁用,不管递归触发器的配置是什么都将被禁用。而在递归触发器中,inserted表和deleted表都只包含被上一次触发器影响的行数据。
    递归触发器有以下两种不同类型(这边没有合适的应用示例可举,先不举例了)

     

    三:触发器的分类

     SqlServer包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。

    1.DML(数据操作语言,Data Manipulation Language)触发器

     DML触发器是一些附加在特定表或视图上的操作代码,当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种:

    1. insert触发器:向表中插入数据时被触发;
    2. delete触发器:从表中删除数据时被触发;
    3. update触发器:修改表中数据时被触发。

    当遇到下列情形时,应考虑使用DML触发器:

    1. 通过数据库中的相关表实现级联更改
    2. 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他限制。
    3. 评估数据修改前后表的状态,并根据该差异才去措施。

    2.DDL(数据定义语言,Data Definition Language)触发器

     DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用,使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

    3.登录触发器

        登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败,将不激发登录触发器。

    三:触发器的分类

     SqlServer包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。

    1.DML(数据操作语言,Data Manipulation Language)触发器

     DML触发器是一些附加在特定表或视图上的操作代码,当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种:

    1. insert触发器:向表中插入数据时被触发;
    2. delete触发器:从表中删除数据时被触发;
    3. update触发器:修改表中数据时被触发。

    当遇到下列情形时,应考虑使用DML触发器:

    1. 通过数据库中的相关表实现级联更改
    2. 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他限制。
    3. 评估数据修改前后表的状态,并根据该差异才去措施。

    2.DDL(数据定义语言,Data Definition Language)触发器

     DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用,使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

    3.登录触发器

        登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败,将不激发登录触发器。

    回到顶部

    1.4.管理触发器

    禁用和启用触发器
    执行下列语句禁用和启用触发器

    ALTER TABLE student DISABLE TRIGGER update_stu_no_single
    --禁用update_stu_no_single触发器
    GO
    ALTER TABLE student ENABLE TRIGGER update_stu_no_single
    --启用update_stu_no_single触发器
    GO
    

    执行下列语句禁用和启用数据库级别触发器

    DISABLE TRIGGER deny_DDL_table ON DATABASE
    --禁用数据库级别触发器deny_DDL_table
    GO
    ENABLE TRIGGER deny_DDL_table ON DATABASE
    --启用数据库级别触发器deny_DDL_table
    GO
    

              提醒和保护:

      触发器与存储过程的区别是运行方式的不同,触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户,应用程序或者触发器来显示地调用并执行。

    五:创建触发器

     创建触发器的语法: 

    图片 17;)

    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指定触发器的类型
    

    图片 18;)

     准备测试数据:

    图片 19;)

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

    图片 20;)

    1.创建insert触发器

    图片 21;)

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

    图片 22;)

    图片 23;)

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

    图片 24;)

    执行上面的语句后,结果如下图所示:

    图片 25

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

    图片 26;)

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

    图片 27;)

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

    结果如下:

    图片 28

     2.创建delete触发器

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

    图片 29;)

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

    图片 30;)

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

    结果如下:

    图片 31

     3.创建UPDATE触发器

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

    图片 32;)

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

    图片 33;)

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

    图片 34

     4.创建替代触发器

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

    图片 35;)

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

    图片 36;)

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

    图片 37

    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.3.3.3.禁用和启用嵌套触发器

    EXEC sp_configure 'nested triggers',0;
    GO
    --禁用嵌套触发器
    EXEC sp_configure 'nested triggers',1;
    GO
    --启用嵌套触发器
    

    语法

    六:管理触发器 

    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:表示可以删除多个触发器的占位符       
    

    概念:

    目录

    1 CREATE TRIGGER 触发器名称
    2 ON 表名
    3 { FOR | AFTER | INSTEAD OF } 
    4 { [ INSERT ] [ , ] [ DELETE ] [ , ] 
    5    [UPDATE ] }
    6 AS 
    7   SQL 语句 [ ... n ] 
    

    概念:

    一:触发器的优点

     1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

     2.触发器可以通过数据库中的相关表进行层叠修改。

     3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。

    回到顶部

    1.3.3.1.嵌套触发器

    如果一个触发器在执行操作时引发了另一个触发器,而这个触发器又引发了下一个触发器,那么这些触发器就是嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用sp_configure存储过程禁用和重新启用嵌套。
    DML触发器和DDL触发器最多可以嵌套32层,可以通过nested triggers来配置是否可以嵌套AFTER触发器,但是不管此设置如何都可以嵌套INSTEAD OF触发器。如果嵌套触发器进入了无限循环,该触发器将被终止,并且回滚整个事务。嵌套触发器具有多种用途,比如保存前一个触发器所影响的行的副本。
    使用嵌套触发器时应该注意以下几点:

    • 默认情况下,嵌套触发器配置选项开启。
    • 在同一个触发器事务中,一个触发器不会被触发两次,触发器不会调用他自己来响应触发器中对同一个表的第二次更新
    • 由于触发器是一个事务,一旦嵌套中任何一层的触发器出现错误,将回滚整个事务。

    示例11:有teacher_course表(教师所教课程表),course表(课程表)和course_selection表(学生选课表),写一个嵌套触发器,实现课程取消后,删除教师所教课程表中关于该课程的记录,而教师所教课程表中该课程的记录被取消,导致该课程的学生选课记录也做相应取消。
    执行下列语句

    --创建course表上的触发器,删除course表中的课程,teacher_course表中的记录做对应删除
    CREATE TRIGGER course_delete_batch
    ON course
    FOR DELETE
    AS
    DECLARE @course_id CHAR(4)
    DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT course_id FROM deleted
    OPEN course_cursor
    FETCH NEXT FROM course_cursor INTO @course_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    DELETE FROM teacher_course WHERE course_id=@course_id
    FETCH NEXT FROM course_cursor INTO @course_id
    END
    GO
    --创建teacher_course表上的触发器,删除教师课程表的记录,学生选课表的记录也做对应删除
    CREATE TRIGGER teacher_course_delete_batch
    ON teacher_course
    FOR DELETE
    AS
    DECLARE @course_id CHAR(4)
    DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT course_id FROM deleted
    OPEN teacher_course_cursor
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
    DELETE FROM course_selection WHERE course_id=@course_id
    ELSE
    PRINT 'course_id为' @course_id '的课程依然正常开课,该课程的学生选课情况不予删除'
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    END
    GO
    

    course_delete_batch和** teacher_course_delete_batch就形成了一个嵌套触发器,下面来验证嵌套触发器的正确性。 Course表中的数据如图所示
    图片 38
    Teacher_course表中的数据如图所示
    图片 39
    Course_selection**表中的数据如图所示
    图片 40
    以课程0013为例,执行下列语句

    DELETE FROM course WHERE course_id='0013'
    

    Course表的数据如图所示
    图片 41
    Teacher_course表的数据如图所示
    图片 42
    Course_selection表的数据如图所示
    图片 43
    所有关于0013课程的数据都被删除。嵌套触发器有效。

    注:在触发器teacher_course_delete_batch中,我额外加入了一个判断,当teacher_course表中还有老师在教授这门课程时,所有关于这门课程的学生选课信息都不予删除。这样做在嵌套触发器里是多余的,删除一门课程,必然会删除teacher_course表中所有与这门课程有关的记录,也必然删除course_selection表中所有与这门课程有关的记录,但是,这样做可以保证该触发器能够独立于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能用于其他嵌套触发器中,看示例12

    示例12:有teacher表(教师信息表),teacher_course(教师所教课程表),和course_selection表(学生选课记录表),写一个嵌套触发器,实现当一个教师离职时,在删除该教师所教课程信息,如果没有教师教这门课程,再删除该课程选课记录。
    其中teacher_course表的触发器teacher_course_delete_batch已经在示例11中写完,只需创建teacher表的teacher_delete_batch触发器即可
    执行下列代码

    CREATE TRIGGER teacher_delete_batch
    ON teacher
    FOR DELETE
    AS
    DECLARE @teacher_id CHAR(4)
    DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT teacher_id FROM deleted
    OPEN teacher_cursor
    FETCH NEXT FROM teacher_cursor INTO @teacher_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    DELETE FROM teacher_course WHERE teacher_id=@teacher_id
    FETCH NEXT FROM teacher_cursor INTO @teacher_id
    END
    GO
    

    测试嵌套触发器的正确性
    Teacher表的数据如图所示
    图片 44
    Teacher_course表的数据如图所示
    图片 45
    Course_selection表的数据如图所示
    图片 46
    以删除0012号教师路易为例,0012号教师教授0013号课程,且teacher_course表中并无其他教师教授0013号课程,按照逻辑要删除teacher_course表中0012号教师的所教课程记录和course_selection表中所有0013号课程的选课记录。执行下列语句

    DELETE FROM teacher WHERE teacher_id='0012'
    

    Teacher表的数据如图所示
    图片 47
    Teacher_course表的数据如图所示
    图片 48
    Course_selection表的数据如图所示
    图片 49
    测试结果正确
    参照上面的数据,继续测试另一种情况,以删除0011号教师卢含笑为例,0011号教师教授0012号课程,在teacher_course表中还有其他教师教授该课程,因此嵌套触发器会删除teacher_course表中关于0011号教师教授课程记录,但不会删除course_selection表中关于0012号课程的选课记录。执行下列语句

    DELETE FROM teacher WHERE teacher_id='0011'
    GO
    

    结果如图所示
    图片 50
    Teacher表的数据如图所示
    图片 51
    Teacher_course表的数据如图所示
    图片 52
    Course_selection表的数据如图所示
    图片 53

            **删除触发器:**

    触发器和存储过程的区别:

    回到顶部

    1.3.创建触发器

              开启和禁用:

    四:触发器的工作原理

    触发器触发时:

    1. 系统自动在内存中创建deleted表或inserted表;
    2. 只读,不允许修改,触发器执行完成后,自动删除。

    inserted表:

    1. 临时保存了插入或更新后的记录行;
    2. 可以从inserted表中检查插入的数据是否满足业务需求;
    3. 如果不满足,则向用户发送报告错误消息,并回滚插入操作。 

    deleted表:

    1. 临时保存了删除或更新前的记录行;
    2. 可以从deleted表中检查被删除的数据是否满足业务需求;
    3. 如果不满足,则向用户报告错误消息,并回滚插入操作。

    inserted表和deleted表对照: 

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

     

     

     

    图片 54

      触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。

    1.3.3.嵌套触发器

          (1)AFTER触发器:它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。它主要是用于记录变更后的处理或检查,一旦发生错误,可以用Rollback Transaction语句来回滚本次扣件,不过不能对视图定义AFTER触发器。       (2)INSTEAD OF触发器:它在执行INSERT、UPDATE、DELETE语句操作之前执行触发器本身所定义的操作。而INSTEAD OF触发器是可以定义在视图上的。

    • 一:触发器的优点
    • 二:触发器的作用
    • 三:触发器的分类
    • 四:触发器的工作原理
    • 五:创建触发器
    • 六:管理触发器 

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:2008从入门到精通,触发器入门

    关键词: