精华内容
下载资源
问答
  • 一、什么是触发器? 1、mysql触发器是:在当前的数据库表中设置一个对每行数据的...二、创建触发器 create trigger 触发器名字 事件 执行代码; 执行代码就是sql语句或sql语句块。 1、事件内容: 插入:insert ...

    一、什么是触发器?

    1、mysql触发器是:在当前的数据库表中设置一个对每行数据的一个监听器,监听相关事件,每当事件发生时。会执行一段由sql完成的一段功能代码。这段功能代码也是sql语句。

    2、触发器的元素:

    事件,执行代码。

    二、创建触发器

    create trigger 触发器名字 事件 执行代码;

    执行代码就是sql语句或sql语句块。

    1、事件内容:

    插入:insert 删除:delete  修改:update

    事件的时机:执行之前,执行之后。内容和时机组合共有六种事件。

    Before insert  ,Before delete ,before update ,after insert ,after delete ,after update.

    注意:这个时机,是针对其后表的内容的监控。

    比如:

    create trigger jiaobanfei after update on t_student 
    
    for each row 
    
    update t_class set t_class_money=t_class_money+20;

    此处的after update on是说t_student表的每一行更新后,就立即更新t_class表中的t_class_money字段。

    2、具体语法

    删除触发器:

    drop trigger 触发器名字;

    注意事项:

    1、触发器不能同名

    2、目前mysql只支持一类事件设置一个触发器。

    三、管理触发器

    1、查看触发器:

    show create trigger trigger_name;

    2、在触发器中获取触发该触发程序时的数据。

    利用触发程序内的new 和old来完成。

    注意:insert中不能使用old,delete中不能使用new。

    3、多条sql语句下的触发器

    sql语句块用begin   end包裹。

    整个触发器语句用delimiter包裹。

    例如:

    delimiter $$
        create trigger ruxue after insert on czbk_student
        for each row
        begin
            update class set stu_count=stu_count+1;
            update class set cz_money=zc_money+2e;
        end
    $$
    delimiter;

     

    展开全文
  • 创建触发器 在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现,其语法...参数BEFOREAFTER指定了触发器执行的时间,前者在触发器事件之前执行触发器语 句,后者在触发器事件之后执行触发器语句; 参数trigger_EV

    创建触发器

    在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现,其语法形式如下:

    CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT
    ON TABLE_NAME FOR EACH ROW trigger_STMT

    在上述语句中,参数trigger_name表示要创建的触发器名;

    参数BEFORE和AFTER指定了触发器执行的时间,前者在触发器事件之前执行触发器语 句,后者在触发器事件之后执行触发器语句;

    参数trigger_EVENT表示触发事件,即触发器执行条件,包含DELETE、INSERT和UPDATE语句;参数TABLE_NAME表示触发事件的操作表名;参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器;

    参数trigger_STMT表示激活触发器后被执行的语句。执行语句中如果要引用更新记录 中的字段,对于INSERT语句,只有NEW是合法的,表示当前已插入的记录;对于 DELETE语句,只有OLD才合法,表示当前删除的记录;而UPDATE语句可以和 NEW(更新后)以及OLD(更新前)同时使用。

    注意:不能创建具有相同名字的触发器。另外,对于具有相同触发程序动作时间和事件的给定表,不能有两个触发器。因此,对于有经验的用户,在创建触发器之前,需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。

    【示例10-1】执行SQL语句CREATE TRIGGER,在数据库school中存在两个表对象:学员表student和班级表 class,创建触发器实现向学员表中插入记录时,就会在插入之后更新班级表中的人数,当我们删除某条学员的记录时,就会在删除后更新班级表中的人数,具体步骤如下:

    mysql>  use school;   #选择数据库school                                           
    mysql>  CREATE TABLE class (                                                        
      `id` int NOT NULL AUTO_INCREMENT,                                              
      `name` varchar(128) DEFAULT NULL,                                               
      `teacher` varchar(64) DEFAULT NULL,  
      `count`  int DEFAULT 0,                                           
      UNIQUE KEY `id` (`id`)                                                              
    );  #创建班级表 class                                                                 
    mysql> insert into class values(101, '萌新一班', 'Martin', 0),(102, '萌新二班', 'Rock', 0),(103, '萌新三班', 'Janny', 0);  #创建成绩表 grade                                                 
    mysql>  CREATE TABLE `student` (                                                  
      `id` int NOT NULL AUTO_INCREMENT UNIQUE,                                                            
      `name` varchar(64) DEFAULT NULL,                                                
      `class_id` int DEFAULT NULL,                                                      
      `sex` enum('F','M') DEFAULT NULL                                                  
    ); 
    mysql> create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id;   #创建触发器,新增学员班级人数增1
                                                                                   
    mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F');  #插入多条记录   
    mysql> select count from class  ;  #查询class 表人数  
    mysql> create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; #创建触发器,删除学员班级人数减1
    

    触发器包含多条执行语句

       CREATE   trigger trigger_name BEFORE|AFTER trigger_EVENT     
       ON TABLE_NAME FOR EACH ROW                                 
           BEGIN                                                        
            trigger_STMT                                                 
           END         
    

    在上述语句中,比“只有一条执行语句的触发器”语法多出来两个关键字BEGIN和END,在这两个关键字之间是所要执行的多个执行语句的内容,执行语句之间用分号隔开。

    在MySQL中,一般情况下用“;”符号作为语句的结束符号,可是在创建触发器时,需要用到“;”符号作为执行语句的结束符号。为了解决该问题,可以使用关键字DELIMITER语句。例如,“DELIMITER ”可以将结束符号设置成“”。

    mysql>  use school;   #选择数据库school         
    mysql>  create table grade(id int UNIQUE AUTO_INCREMENT,  math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned);       #创建成绩表 grade   
    mysql> insert into grade values(1, 80, 87, 91),(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89);  #插入多条记录                                      
    mysql> DELIMITER $$                                                                                
    mysql> create trigger tri_delete_student after delete on student for each row 
            BEGIN                                    
             Delete from grade where id = OLD.id;  #删除成绩表中的记录                                                        
             update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录   
             END;                                    
             $$                                       
             DELIMITER ;
    

    具体操作

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    触发器包含多条执行语句
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    结语:

    时间: 2020-07-13

    展开全文
  • SQLServer之创建AFETER DELETE触发器

    千次阅读 2018-10-12 13:31:43
    触发器触发时,系统自动在内存中创建deleted表或inserted表,inserted表临时保存了插入或更新后的记录行,deleted表临时保存了删除或更新前的记录行,内存中创建的表只读,不允许修改,触发器执行完成后,自动删除。...

    DML AFTER DELETE触发器创建原理

    触发器触发时,系统自动在内存中创建deleted表或inserted表,inserted表临时保存了插入或更新后的记录行,deleted表临时保存了删除或更新前的记录行,内存中创建的表只读,不允许修改,触发器执行完成后,自动删除。

    delete触发器工作原理:第一步执行delete删除语句,删除表中的数据行,第二步触发delete删除触发器,向系统临时表的deleted表中插入被删除的副本,第三步触发器检查deleted中被删除的数据,确定是否需要回滚或执行其他操作。

    不能使用SSMS数据库管理工具直接创建DML添加触发器,可以使用T-SQL脚本创建DML添加触发器。

    DML AFTER DELETE触发器创建

    语法:

    --声明数据库引用
    use 数据库名;
    go

    --判断是否存在触发器,如果存在则删除
    if exists(select * from sysobjects where name=触发器名)
    drop trigger 触发器名;
    go

    ----创建新的删除触发器
    create

    --触发器标识符
    trigger

    --DML 触发器所属架构的名称。 DML 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 DDL 或登录触发器指定 
    --[dbo.]

    --触发器名称
    [架构名.]触发器名

    on

    --对其执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 INSTEAD OF 触发器引用。 不能对局部或全局临时表定义 DML 触发器。
    [架构名.]{ table | view}

    with
    --对CREATE TRIGGER 语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。(指定此选项将为触发器加密)
    [encryption][,]

    --指示触发器已本机编译。 (只能应用于table)
    --内存优化表上的触发器需要使用此选项。
    [native_compilation][,]

    --确保不能删除或更改触发器引用的表。(只能应用于table)
    --内存优化表上的触发器需要使用此选项,但此选项不支持传统表上的触发器。
    [schemabinding][,]

    --EXECUTE AS (后面可以跟函数,存储过程等)
    --指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
    --内存优化表上的触发器需要使用此选项。
    [execute as clause]

    --FOR | AFTER
    --AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
    --如果仅指定 FOR 关键字,则 AFTER 为默认值。
    --不能对视图定义 AFTER 触发器。
    { for | after}

    --{ [DELETE] [,] [INSERT] [,] [UPDATE] }
    --指定数据修改语句,这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
    --对于 INSTEAD OF 触发器,不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。 同样,也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
    { [insert] [,] [update] [,] [delete] }

    --指定应该再添加一个现有类型的触发器。 WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器,则也不能使用该子句。 
    --仅当为了向后兼容而指定了 FOR 时(但没有 INSTEAD OF 或 AFTER)时,才能使用 WITH APPEND。 如果指定了 EXTERNAL NAME(即触发器为 CLR 触发器),则不能指定 WITH APPEND。
    --with append

    --指示当复制代理修改涉及到触发器的表时,不应执行触发器。
    --not for replication

    as 
    begin
      sql_statement
    end
    end ;
    go

    示例:

    --声明数据库引用
    use testss;
    go

    --判断是否存在触发器,如果存在则删除
    if exists(select * from sysobjects where name='deletetri')
    drop trigger deletetri;
    go

    ----创建新的删除触发器
    create

    --触发器标识符
    trigger

    --DML 触发器所属架构的名称。 DML 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 DDL 或登录触发器指定 
    --[dbo.]

    --触发器名称
    dbo.deletetri

    on

    --对其执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 INSTEAD OF 触发器引用。 不能对局部或全局临时表定义 DML 触发器。
    dbo.test1

    with
    --对CREATE TRIGGER 语句的文本进行模糊处理。使用WITH ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。(指定此选项将为触发器加密)
    encryption,

    --指示触发器已本机编译。 (只能应用于table)
    --内存优化表上的触发器需要使用此选项。
    --native_compilation

    --确保不能删除或更改触发器引用的表。(只能应用于table)
    --内存优化表上的触发器需要使用此选项,但此选项不支持传统表上的触发器。
    schemabinding

    --EXECUTE AS (后面可以跟函数,存储过程等)
    --指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
    --内存优化表上的触发器需要使用此选项。
    --execute as clause

    --FOR | AFTER
    --AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
    --如果仅指定 FOR 关键字,则 AFTER 为默认值。
    --不能对视图定义 AFTER 触发器。
    for

    --{ [DELETE] [,] [INSERT] [,] [UPDATE] }
    --指定数据修改语句,这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
    --对于 INSTEAD OF 触发器,不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。 同样,也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
    delete
    as 
    begin
    declare @sss nvarchar(100)=null;
    set @sss=(select top(1) name from deleted order by id desc)
    if @sss is not null
    begin
    delete from dbo.test2 where id=(select id from dbo.test2 where name=@sss);
    end
    end ;
    go

    示例结果:

    DML AFTER DELETE触发器优缺点

    优点:

      1、实现数据的级联删除操作。

      2、不需要参数,不需要显示调用。

    缺点:

      1、如果不知道表中存在删除器,删除表中数据会造成不必要的级联操作,造成数据丢失。

      2、可移植性差。

      3、占用服务器资源,给服务器造成压力。

      4、执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度。

      5、触发器会使编程时源码的结构被迫打乱,为将程序修改、源码阅读带来困难。

    展开全文
  • 随便搜集了一点觉得还挺全面的 创建触发器触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft® SQL Server™ 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多...
    随便搜集了一点觉得还挺全面的 
    创建触发器触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft® SQL Server™ 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器

    语法
    CREATE TRIGGER trigger_name
    ON { table | view }
    [ WITH ENCRYPTION ]
    {
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
    [ WITH APPEND ]
    [ NOT FOR REPLICATION ]
    AS
    [ { IF UPDATE ( column )
    [ { AND | OR } UPDATE ( column ) ]
    [ ...n ]
    | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
    { comparison_operator } column_bitmask [ ...n ]
    } ]
    sql_statement [ ...n ]
    }
    }

    参数
    trigger_name

    触发器的名称。触发器名称必须符合标识符规则,并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。

    Table | view

    是在其上执行触发器的表或视图,有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

    WITH ENCRYPTION

    加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。

    AFTER

    指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器

    如果仅指定 FOR 关键字,则 AFTER 是默认设置。

    不能在视图上定义 AFTER 触发器

    INSTEAD OF

    指定执行触发器而不是执行触发 SQL 语句,从而替代触发语句的操作。

    在表或视图上,每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而,可以在每个具有 INSTEAD OF 触发器的视图上定义视图。

    INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器

    { [DELETE] [,] [INSERT] [,] [UPDATE] }

    是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个,需用逗号分隔这些选项。

    对于 INSTEAD OF 触发器,不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样,也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。

    WITH APPEND

    指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时,才需要使用该可选子句。如果兼容级别是 70 或更高,则不必使用 WITH APPEND 子句添加现有类型的其它触发器(这是兼容级别设置为 70 或更高的 CREATE TRIGGER 的默认行为)。有关更多信息,请参见 sp_dbcmptlevel。

    WITH APPEND 不能与 INSTEAD OF 触发器一起使用,或者,如果显式声明 AFTER 触发器,也不能使用该子句。只有当出于向后兼容而指定 FOR 时(没有 INSTEAD OF 或 AFTER),才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR(将被解释为 AFTER)。

    NOT FOR REPLICATION

    表示当复制进程更改触发器所涉及的表时,不应执行该触发器

    AS

    触发器要执行的操作。

    sql_statement

    触发器的条件和操作。触发器条件指定其它准则,以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。

    当尝试 DELETE、INSERT 或 UPDATE 操作时,Transact-SQL语句中指定的触发器操作将生效。

    触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据;它不应将数据返回给用户。触发器中的 Transact-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用几个特殊的表:

    deleted 和 inserted 是逻辑(概念)表。这些表在结构上类似于定义触发器的表(也就是在其中尝试用户操作的表);这些表用于保存用户操作可能更改的行的旧值或新值。例如,若要检索 deleted 表中的所有值,请使用:
    SELECT *
    FROM deleted

    如果兼容级别等于 70,那么在 DELETE、INSERT 或 UPDATE 触发器中,SQL Server 将不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值,请将 inserted 表与原始更新表联接。当兼容级别是 65 或更低时,对 inserted 或 deleted 表中允许空值的text、ntext 或 image 列,将返回空值;如果这些列不可为空,则返回零长度字符串。
    当兼容级别是 80 或更高时,SQL Server 允许在表或视图上通过 INSTEAD OF 触发器更新 text、ntext 或 image 列。

    n

    是表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句,可以通过重复 UPDATE (column) 子句包含多列。

    IF UPDATE (column)

    测试在指定的列上进行的 INSERT 或 UPDATE 操作,不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名,所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作,请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值,因为这些列插入了显式值或隐性 (NULL) 值。



    说明 IF UPDATE (column) 子句的功能等同于 IF、IF...ELSE 或 WHILE 语句,并且可以使用 BEGIN...END 语句块。有关更多信息,请参见控制流语言。


    可以在触发器主体中的任意位置使用 UPDATE (column)。

    column

    是要测试 INSERT 或 UPDATE 操作的列名。该列可以是 SQL Server 支持的任何数据类型。但是,计算列不能用于该环境中。有关更多信息,请参见数据类型。

    IF (COLUMNS_UPDATED())

    测试是否插入或更新了提及的列,仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 varbinary 位模式,表示插入或更新了表中的哪些列。

    COLUMNS_UPDATED 函数以从左到右的顺序返回位,最左边的为最不重要的位。最左边的位表示表中的第一列;向右的下一位表示第二列,依此类推。如果在表上创建的触发器包含 8 列以上,则 COLUMNS_UPDATED 返回多个字节,最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值,因为这些列插入了显式值或隐性 (NULL) 值。

    可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。

    bitwise_operator

    是用于比较运算的位运算符。

    updated_bitmask

    是整型位掩码,表示实际更新或插入的列。例如,表 t1 包含列 C1、C2、C3、C4 和 C5。假定表 t1 上有 UPDATE 触发器,若要检查列 C2、C3 和 C4 是否都有更新,指定值 14;若要检查是否只有列 C2 有更新,指定值 2。

    comparison_operator

    是比较运算符。使用等号 (=) 检查 updated_bitmask 中指定的所有列是否都实际进行了更新。使用大于号 (>) 检查 updated_bitmask 中指定的任一列或某些列是否已更新。

    column_bitmask

    是要检查的列的整型位掩码,用来检查是否已更新或插入了这些列。

    注释
    触发器常常用于强制业务规则和数据完整性。SQL Server 通过表创建语句(ALTER TABLE 和 CREATE TABLE)提供声明引用完整性 (DRI);但是 DRI 不提供数据库间的引用完整性。若要强制引用完整性(有关表的主键和外键之间关系的规则),请使用主键和外键约束(ALTER TABLE 和 CREATE TABLE 的 PRIMARY KEY 和 FOREIGN KEY 关键字)。如果触发器表存在约束,则在 INSTEAD OF 触发器执行之后和 AFTER 触发器执行之前检查这些约束。如果违反了约束,则回滚 INSTEAD OF 触发器操作且不执行(激发)AFTER 触发器

    可用 sp_settriggerorder 指定表上第一个和最后一个执行的 AFTER 触发器。在表上只能为每个 INSERT、UPDATE 和 DELETE 操作指定一个第一个执行和一个最后一个执行的 AFTER 触发器。如果同一表上还有其它 AFTER 触发器,则这些触发器将以随机顺序执行。

    如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器,则将除去已修改触发器上设置的第一个或最后一个特性,而且必须用 sp_settriggerorder 重置排序值。

    只有当触发 SQL 语句(包括所有与更新或删除的对象关联的引用级联操作和约束检查)成功执行后,AFTER 触发器才会执行。AFTER 触发器检查触发语句的运行效果,以及所有由触发语句引起的 UPDATE 和 DELETE 引用级联操作的效果。

    触发器限制
    CREATE TRIGGER 必须是批处理中的第一条语句,并且只能应用到一个表中。

    触发器只能在当前的数据库中创建,不过触发器可以引用当前数据库外部对象。

    如果指定触发器所有者名称以限定触发器,请以相同的方式限定表名。

    在同一条 CREATE TRIGGER 语句中,可以为多种用户操作(如 INSERT 和 UPDATE)定义相同的触发器操作。

    如果一个表的外键在 DELETE/UPDATE 操作上定义了级联,则不能在该表上定义 INSTEAD OF DELETE/UPDATE 触发器

    触发器内可以指定任意的 SET 语句。所选择的 SET 选项在触发器执行期间有效,并在触发器执行完后恢复到以前的设置。

    与使用存储过程一样,当触发器激发时,将向调用应用程序返回结果。若要避免由于触发器激发而向应用程序返回结果,请不要包含返回结果的 SELECT 语句,也不要包含在触发器中进行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理;这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值,则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。

    DELETE 触发器不能捕获 TRUNCATE TABLE 语句。尽管 TRUNCATE TABLE 语句实际上是没有 WHERE 子句的 DELETE(它删除所有行),但它是无日志记录的,因而不能执行触发器。因为 TRUNCATE TABLE 语句的权限默认授予表所有者且不可转让,所以只有表所有者才需要考虑无意中用 TRUNCATE TABLE 语句规避 DELETE 触发器的问题。

    无论有日志记录还是无日志记录,WRITETEXT 语句都不激活触发器

    触发器中不允许以下 Transact-SQL 语句:

    ALTER DATABASE CREATE DATABASE DISK INIT
    DISK RESIZE DROP DATABASE LOAD DATABASE
    LOAD LOG RECONFIGURE RESTORE DATABASE
    RESTORE LOG




    说明 由于 SQL Server 不支持系统表中的用户定义触发器,因此建议不要在系统表中创建用户定义触发器


    多个触发器
    SQL Server 允许为每个数据修改事件(DELETE、INSERT 或 UPDATE)创建多个触发器。例如,如果对已有 UPDATE 触发器的表执行 CREATE TRIGGER FOR UPDATE,则将创建另一个更新触发器。在早期版本中,在每个表上,每个数据修改事件(INSERT、UPDATE 或 DELETE)只允许有一个触发器



    说明 如果触发器名称不同,则 CREATE TRIGGER(兼容级别为 70)的默认行为是在现有的触发器中添加其它触发器。如果触发器名称相同,则 SQL Server 返回一条错误信息。但是,如果兼容级别等于或小于 65,则使用 CREATE TRIGGER 语句创建的新触发器将替换同一类型的任何现有触发器,即使触发器名称不同。有关更多信息,请参见 sp_dbcmptlevel。


    递归触发器
    当在 sp_dboption 中启用 recursive triggers 设置时,SQL Server 还允许触发器的递归调用。

    递归触发器允许发生两种类型的递归:

    间接递归


    直接递归
    使用间接递归时,应用程序更新表 T1,从而激发触发器 TR1,该触发器更新表 T2。在这种情况下,触发器 T2 将激发并更新 T1。

    使用直接递归时,应用程序更新表 T1,从而激发触发器 TR1,该触发器更新表 T1。由于表 T1 被更新,触发器 TR1 再次激发,依此类推。

    下例既使用了间接触发器递归,又使用了直接触发器递归。假定在表 T1 中定义了两个更新触发器 TR1 和 TR2。触发器 TR1 递归地更新表 T1。UPDATE 语句使 TR1 和 TR2 各执行一次。而 TR1 的执行将触发 TR1(递归)和 TR2 的执行。给定触发器的 inserted 和 deleted 表只包含与唤醒调用触发器的 UPDATE 语句相对应的行。



    说明 只有启用 sp_dboption 的 recursive triggers 设置,才会发生上述行为。对于为给定事件定义的多个触发器,并没有确定的执行顺序。每个触发器都应是自包含的。


    禁用 recursive triggers 设置只能禁止直接递归。若要也禁用间接递归,请使用 sp_configure 将 nested triggers 服务器选项设置为 0。

    如果任一触发器执行了 ROLLBACK TRANSACTION 语句,则无论嵌套级是多少,都不会进一步执行其它触发器

    嵌套触发器
    触发器最多可以嵌套 32 层。如果一个触发器更改了包含另一个触发器的表,则第二个触发器将激活,然后该触发器可以再调用第三个触发器,依此类推。如果链中任意一个触发器引发了无限循环,则会超出嵌套级限制,从而导致取消触发器。若要禁用嵌套触发器,请用 sp_configure 将 nested triggers 选项设置为 0(关闭)。默认配置允许嵌套触发器。如果嵌套触发器是关闭的,则也将禁用递归触发器,与 sp_dboption 的 recursive triggers 设置无关。

    延迟名称解析
    SQL Server 允许 Transact-SQL 存储过程、触发器和批处理引用编译时不存在的表。这种能力称为延迟名称解析。但是,如果 Transact-SQL 存储过程、触发器或批处理引用在存储过程或触发器中定义的表,则只有当兼容级别设置(通过执行 sp_dbcmptlevel 设置)等于 65 时,才会在创建时发出警告。如果使用批处理,则在编译时发出警告。如果引用的表不存在,将在运行时返回错误信息。有关更多信息,请参见延迟名称解析和编译。

    权限
    CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员,并且不可转让。

    若要检索表或视图中的数据,用户必须在表或视图中拥有 SELECT 语句权限。若要更新表或视图的内容,用户必须在表或视图中拥有 INSERT、DELETE 和 UPDATE 语句权限。

    如果视图中存在 INSTEAD OF 触发器,用户必须在该视图中有 INSERT、DELETE 和 UPDATE 特权,以对该视图发出 INSERT、DELETE 和 UPDATE 语句,而不管实际上是否在视图上执行了这样的操作。

    示例
    A. 使用带有提醒消息的触发器
    当有人试图在 titles 表中添加或更改数据时,下例将向客户端显示一条消息。



    说明 消息 50009 是 sysmessages 中的用户定义消息。有关创建用户定义消息的更多信息,请参见 sp_addmessage。


    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'reminder' AND type = 'TR')
    DROP TRIGGER reminder
    GO
    CREATE TRIGGER reminder
    ON titles
    FOR INSERT, UPDATE
    AS RAISERROR (50009, 16, 10)
    GO

    B. 使用带有提醒电子邮件的触发器
    当 titles 表更改时,下例将电子邮件发送给指定的人员 (MaryM)。

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'reminder' AND type = 'TR')
    DROP TRIGGER reminder
    GO
    CREATE TRIGGER reminder
    ON titles
    FOR INSERT, UPDATE, DELETE
    AS
    EXEC master..xp_sendmail 'MaryM',
    'Don''t forget to print a report for the distributors.'
    GO

    C. 在 employee 和 jobs 表之间使用触发器业务规则
    由于 CHECK 约束只能引用定义了列级或表级约束的列,表间的任何约束(在下例中是指业务规则)都必须定义为触发器

    下例创建一个触发器,当插入或更新雇员工作级别 (job_lvls) 时,该触发器检查指定雇员的工作级别(由此决定薪水)是否处于为该工作定义的范围内。若要获得适当的范围,必须引用 jobs 表。

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'employee_insupd' AND type = 'TR')
    DROP TRIGGER employee_insupd
    GO
    CREATE TRIGGER employee_insupd
    ON employee
    FOR INSERT, UPDATE
    AS
    /* Get the range of level for this job type from the jobs table. */
    DECLARE @min_lvl tinyint,
    @max_lvl tinyint,
    @emp_lvl tinyint,
    @job_id smallint
    SELECT @min_lvl = min_lvl,
    @max_lvl = max_lvl,
    @emp_lvl = i.job_lvl,
    @job_id = i.job_id
    FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
    JOIN jobs j ON j.job_id = i.job_id
    IF (@job_id = 1) and (@emp_lvl <> 10)
    BEGIN
    RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
    ROLLBACK TRANSACTION
    END
    ELSE
    IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
    BEGIN
    RAISERROR ('The level for job_id:%d should be between %d and %d.',
    16, 1, @job_id, @min_lvl, @max_lvl)
    ROLLBACK TRANSACTION
    END

    D. 使用延迟名称解析
    下例创建两个触发器以说明延迟名称解析。

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'trig1' AND type = 'TR')
    DROP TRIGGER trig1
    GO
    -- Creating a trigger on a nonexistent table.
    CREATE TRIGGER trig1
    on authors
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT a.au_lname, a.au_fname, x.info
    FROM authors a INNER JOIN does_not_exist x
    ON a.au_id = x.au_id
    GO
    -- Here is the statement to actually see the text of the trigger.
    SELECT o.id, c.text
    FROM sysobjects o INNER JOIN syscomments c
    ON o.id = c.id
    WHERE o.type = 'TR' and o.name = 'trig1'

    -- Creating a trigger on an existing table, but with a nonexistent
    -- column.
    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'trig2' AND type = 'TR')
    DROP TRIGGER trig2
    GO
    CREATE TRIGGER trig2
    ON authors
    FOR INSERT, UPDATE
    AS
    DECLARE @fax varchar(12)
    SELECT @fax = phone
    FROM authors
    GO
    -- Here is the statement to actually see the text of the trigger.
    SELECT o.id, c.text
    FROM sysobjects o INNER JOIN syscomments c
    ON o.id = c.id
    WHERE o.type = 'TR' and o.name = 'trig2'

    E. 使用 COLUMNS_UPDATED
    下例创建两个表:一个 employeeData 表和一个 auditEmployeeData 表。人力资源部的成员可以修改 employeeData 表,该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户,则生成审核记录并插入到 auditEmployeeData 审核表。

    通过使用 COLUMNS_UPDATED() 功能,可以快速测试对这些包含敏感雇员信息的列所做的更改。只有在试图检测对表中的前 8 列所做的更改时,COLUMNS_UPDATED() 才起作用。

    USE pubs
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'employeeData')
    DROP TABLE employeeData
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'auditEmployeeData')
    DROP TABLE auditEmployeeData
    GO
    CREATE TABLE employeeData (
    emp_id int NOT NULL,
    emp_bankAccountNumber char (10) NOT NULL,
    emp_salary int NOT NULL,
    emp_SSN char (11) NOT NULL,
    emp_lname nchar (32) NOT NULL,
    emp_fname nchar (32) NOT NULL,
    emp_manager int NOT NULL
    )
    GO
    CREATE TABLE auditEmployeeData (
    audit_log_id uniqueidentifier DEFAULT NEWID(),
    audit_log_type char (3) NOT NULL,
    audit_emp_id int NOT NULL,
    audit_emp_bankAccountNumber char (10) NULL,
    audit_emp_salary int NULL,
    audit_emp_SSN char (11) NULL,
    audit_user sysname DEFAULT SUSER_SNAME(),
    audit_changed datetime DEFAULT GETDATE()
    )
    GO
    CREATE TRIGGER updEmployeeData
    ON employeeData
    FOR update AS
    /*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/

    IF (COLUMNS_UPDATED() & 14) > 0
    /*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
    BEGIN
    -- Audit OLD record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    audit_emp_id,
    audit_emp_bankAccountNumber,
    audit_emp_salary,
    audit_emp_SSN)
    SELECT 'OLD',
    del.emp_id,
    del.emp_bankAccountNumber,
    del.emp_salary,
    del.emp_SSN
    FROM deleted del

    -- Audit NEW record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    audit_emp_id,
    audit_emp_bankAccountNumber,
    audit_emp_salary,
    audit_emp_SSN)
    SELECT 'NEW',
    ins.emp_id,
    ins.emp_bankAccountNumber,
    ins.emp_salary,
    ins.emp_SSN
    FROM inserted ins
    END
    GO

    /*Inserting a new employee does not cause the UPDATE trigger to fire.*/
    INSERT INTO employeeData
    VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
    GO

    /*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/

    UPDATE employeeData
    SET emp_salary = 51000
    WHERE emp_id = 101
    GO
    SELECT * FROM auditEmployeeData
    GO

    /*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/

    UPDATE employeeData
    SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
    WHERE emp_id = 101
    GO
    SELECT * FROM auditEmployeeData
    GO

    F. 使用 COLUMNS_UPDATED 测试 8 列以上
    如果必须测试影响到表中前 8 列以外的列的更新时,必须使用 UBSTRING 函数测试由 COLUMNS_UPDATED 返回的适当的位。下例测试影响 Northwind.dbo.Customers 表中的第 3、第 5 或第 9 列的更新。

    USE Northwind
    DROP TRIGGER tr1
    GO
    CREATE TRIGGER tr1 ON Customers
    FOR UPDATE AS
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
    + power(2,(5-1)))
    AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
    )
    PRINT 'Columns 3, 5 and 9 updated'
    GO

    UPDATE Customers
    SET ContactName=ContactName,
    Address=Address,
    Country=Country
    GO
    展开全文
  • mysql 索引的创建方式 // 1. 主键索引 alter table 表名 add primary key(列名); // 2. 普通索引 alter table 表名 add index 索引名(列名); // 3. 唯一索引 alter table 表名 add unique 索引名(列名); ...
  • 触发器(TRIGGER)是由时间来触发某个操作,这些时间包括insert语句delete语句,当数据系统执行这些事件的时候,就会激活触发器执行的相应的操作 1.创建触发器 在Mysql中创建触发器通过SQL语句CREATE TRIGGER来实现...
  • --1、创建触发器 create trigger tesetTrigger --触发器名称 on employee --在哪个表上定义触发器 for insert --触发器类型 as --程序语句 begin print 'tesetTrigger触发器被使用了'; end --2、调用 ...
  • 创建触发器

    2008-07-08 15:31:42
    创建触发器2006年10月19日 星期四 13:37Transact-SQL 参考 CREATE TRIGGER 创建触发器触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft? SQL Server? 允许...
  • 触发器

    2020-03-14 00:14:25
    触发器关键字:trigger 基本作用:通过对表进行数据的插入、更新或删除等操作来触发,用于执行某些操作的。 触发器的创建 创建语句格式: create trigger 触发器名 #[触发器名] 触发时机 #触发时机:before|after ...
  • 触发器和游标

    2021-03-15 09:34:00
    触发器简介: 触发器是表关联的特殊储存过程,可以在插入、修改、删除表中的数据时触发执行,比数据库本身标准的功能有更精细更复杂的数据控制能力;...trigger : 触发器关键字 after :触发时机
  • 在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现,其语法形式如下: CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW trigger_STMT 在上述语句中,参数trigger_name表示要...
  • 摘要:以下的文章主要介绍的是MySQL触发器的正确创建步骤,以及MySQL触发器删除,以下就是具体内容的描述,希望在你今后的学习中会有所帮助。 标签:MySQL触发器 下面的文章主要描述的是MySQL触发器的正确...
  • 下面的文章主要描述的是MySQL触发器的正确创建步骤,MySQL触发器删除,你如果对MySQL触发器的正确创建步骤,MySQL触发器删除有兴趣的话你就可以点击以下的文章进行观看了。 1、创建MySQL触发器: 语法: ...
  • 下面的文章主要描述的是mysql触发器的正确创建步骤,MySQL触发器删除,你如果对MySQL触发器的正确创建步骤,MySQL触发器删除有兴趣的话你就可以点击以下的文章进行观看了。 1、创建MySQL触发器: 语法: ...
  • 创建oracle触发器

    2008-06-10 12:29:50
    //通过触发器可实现帖子自动回复,指定非法关键字留言自动删除等功能  create trigger &lt;触发器名称&gt; before|after //执行前触发还是执行后触发 insert|update|delete on &lt;表名&gt;...
  • MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象,...创建触发器 代码如下: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 其中: trigger_...
  • 创建Mysql触发器的语法介绍

    千次阅读 2013-11-05 11:43:25
    Mysql触发器是Mysql数据库非常重要的部分,下文对创建Mysql触发器删除Mysql触发器作了详细的介绍,希望对您有所帮助。 1、创建Mysql触发器: 语法: CREATE TRIGGER trigger_name trigger_time trigger_event ON ...
  • mysql 创建触发器

    2012-09-20 16:13:36
    Mysql 创建触发器需要修改默认的截止符号,默认为分号,但是分号会出现在触发器的beginend中间,导致命令执行错误, mysql -h localhost -u root -p123456 -D test 修改截止符号命令如下: delimiter //  ...
  • MySQL(触发器创建和使用)

    千次阅读 2018-07-06 14:31:30
    什么是触发器 触发器(TRIGGER)是MySQL的数据库对象之一,从5.0.2版本开始支持。该对象与编程语言中的函数非常类似,都需要声明、执行等。但是触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、...
  • oracle 创建触发器

    2011-12-26 18:00:50
    一.什么是触发器 触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。...创建触发器 1.基本语法结构 Create [or replace] trigger [模式.]触发器名 ...
  • MySQL必备触发器

    2020-07-06 08:32:43
    文章目录触发器触发器的基本概念触发器创建触发条件触发时机对象newold查看触发器删除触发器使用触发器制作日志利用错误突破使用限制事件事件的基本概念事件的创建查看事件事件调度器单独开启/禁用某个事件MySql...
  • mysql 触发器创建和使用

    万次阅读 2018-03-05 10:06:36
    什么是触发器触发器(TRIGGER)是MySQL的数据库对象之一,从5.0.2版本开始支持。该对象与编程语言中的函数非常类似,都需要声明、执行等。但是触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、...
  • 也可以写出详细的列名,再进行insert提高多线程环境下性能:INSERT LOW_PRIORITY INTO 优先级较低插入检索出的数据:INSERT INTO 表名 SELECT语句更新和删除数据更新语句: ignore参数,若更新
  • LOGON触发器工作原理 ... 因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。 如果身份验证失败,将不激发登录触发器。 LOGON触发器...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 27,396
精华内容 10,958
关键字:

创建和删除触发器的关键字