• SQLSERVER 中的触发器(实例讲解)


    SQLSERVER 中的触发器

        你可以将触发器理解成现实生活中开关, 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活

        它的分类,sqlserver 中有两类触发器,DML触发器和DDL触发器。触发器可以用来实现对表实施复杂的完整性约束,触发器可通过数据库中的相关表实现级联  更改,可以强制

        比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同,触发器可以引用其它表中的列,例如触发器可以使用另一个表中的 SELECT 比较

       插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE 或

       DELETE)允许采取多个不同的对策以响应同一个修改语句。

       1.DML 触发器

                  DML(数据操纵语言 DATA MANIPULATION LANGUAGE) 触发器:是指在数据库中发生DML事件时将启用,DML事件即是指在表或视图中修改数据的insert,update,delete语句。

       2.DDL 触发器

                 DDL(数据定义语言 DATA DEFINITION LANGUAGE)触发器:是指当服务器或数据库发生(DDL事件时将启用,DDL事件包括表或者索引中create,alter,drop)

       登录触发器:是指当用户登录sql server 实例建立会话时触发。

     3. 理解两个逻辑表(INSERTED,DELETED)

                         INSERTED:要插入(inserted)到数据库中数据的一个副本,保存到inserted中

                         DELETED:保存源数据的一个副本到DELETED中,在数据被操作(update deleted)之前。

        这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。

    换一种方式来解释:

       在delete数据的时候,可以假定数据库将要删除的数据放到一个deleted临时表中,我们可以向读取普通的表一样,select 字段 from deleted 
    而insert的时候道理一样,只不过是把要插入的数据放在inserted表中。
    更新操作可以认为是执行了两个操作,先把那一行记录delete掉,然后再insert,这样update操作实际上就对deleted表和inserted表的操作,所以不会有updated表了。(ps:你可以在sqlserver 中写一些测试例子俩加上印象)

    4.Instead of 触发器 

         1.Instead of触发器用于替代引起触发器执行的T-SQL语句。

         2.Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。

         3. 数据表里的数据禁止修改 。

         4. 有可能要回滚修改的SQL语句,有些判断需要在执行之前进行,所以After触发器并不是最好的选择,Instead Of触发器更合适。

         5.在试图中使用触发器, After触发器不能在视图中使用。

         6.Instead of 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。        

     5.After触发器 

       After触发器在一个Insert,Update或Deleted语句之后执行,

       After触发器只能用于表,一个表的每个修改动作都可以有多个After触发器。

      触发器的执行过程 如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。       

    SQL Server创建的默认的触发器为AFTER触发器

      他们的顺序应该是这样的:

       

    5.基本语法

    create trigger trigger_name           
    
    on {table_name | view_name}           
    
    {for | After | Instead of }   [ insert, update,delete ]           
    
      as  
    
      sql_statement  
      --修改
          -- 把CREATE 改成ALTER
          
          --查看触发器中的语句;
          EXEC SP_HELPTEXT TRIGGER_NAME
          
          --查看数据库中触发器;
          SELECT * FROM SYSOBJECTS WHERE XTYPE='TR'
          
          --删除触发器
           DROP TRIGGER TRIGGER_NAME  
    
          --启用或关闭数据库中触发器;
           DISABLE TRIGGER[NAME] ON DATABASE
    
           ENABLE TRIGGER[NAME] ON DATABASE

    6.我们还是看看具体的实例吧

    应用场景一 完整性约束(修改,删除) --after触发器

    --如果更改学生学号,record 表中的学生也同时改变
    --在关系型数据中,未保持数据的完整和一致性,可能会用主外键来约束
    --在NOSQL,我这里就用触发器;
    --测试数据库
          CREATE TABLE STUDENT(
             STUDENTID VARCHAR(20) PRIMARY KEY ,
             NAME VARCHAR(100)
          )
          GO
          CREATE TABLE RECORD(
             ID INT PRIMARY KEY IDENTITY(1,1),
             BOOKNAME VARCHAR(40),
             STUDENTID VARCHAR(20)
             
          )
          GO
          --测试数据
          INSERT STUDENT VALUES('110','JACK')
          INSERT STUDENT VALUES('111','TOM')
          INSERT RECORD VALUES('傲慢与偏见','110')
          INSERT RECORD VALUES('老人与海','110')
          INSERT RECORD VALUES('我的童年','110')
    
          --如果更改学生学号,record 表中的学生也同时改变
          GO
          ALTER TRIGGER TRG
          ON STUDENT
          FOR UPDATE
          AS  
            IF UPDATE(STUDENTID)
            BEGIN
                 DECLARE @OLDID VARCHAR(20),
                         @NEWID VARCHAR(20)
                         
                 SELECT  @OLDID=STUDENTID FROM DELETED
                 SELECT  @NEWID=STUDENTID FROM INSERTED
                 UPDATE  RECORD SET STUDENTID=@NEWID
                 WHERE   STUDENTID=@OLDID
            END
          
          SELECT * FROM STUDENT
          GO
          UPDATE STUDENT  SET STUDENTID='110_1' WHERE STUDENTID='110' 
          SELECT *FROM RECORD
          
          
        --如果 删除学生号,同时删除它的借书记录

    应用场景二 日志记录 --after触发器

    如果修改学生的命名,这个操作被记录到stulog 表中

    --测试数据库
       CREATE TABLE STUDENT(
        ID   INT PRIMARY KEY IDENTITY(1,1),
        NAME VARCHAR(40) NULL,
        age  INT NULL,
        Sex  VARCHAR(2) NULL
        )
        GO
        CREATE TABLE STULOG
        (
         ID INT PRIMARY KEY IDENTITY(1,1),
         LOGCONTENT VARCHAR(200),
         TIMES DATETIME 
        )
        GO
        --触发器建立
         CREATE TRIGGER TRG
         ON STUDENT
         FOR UPDATE 
         AS
             IF UPDATE(NAME) --某一特定的字段
             BEGIN
                  DECLARE @OLDNAME VARCHAR(30),
                          @NEWNAME VARCHAR(30),
                          @CONTENT VARCHAR(100) 
                  SELECT @OLDNAME=NAME FROM DELETED
                  SELECT @NEWNAME=NAME FROM INSERTED
                  SET @CONTENT=@OLDNAME+' HAS CHANGE TO '+@NEWNAME
                  INSERT STULOG VALUES(@CONTENT,GETDATE())
             END
            GO
            
            --测试
              UPDATE STUDENT SET NAME='JACK' WHERE ID=4
              SELECT * FROM STUDENT
              SELECT * FROM STULOG
           --结果
              -- 4    JACK    24    男 
              -- 1    XX HAS CHANGE TO JACK    2015-09-25 13:49:50.110


    应用场景三 避免重复插入 --instead of触发器

    当添加新商品到购物车中时,检查物品是否已经存在,如果已经存在就修改数量,不存在就添加

    --测试数据库
    CREATE TABLE SHOPPINGCAR(
          ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
          ITEMID VARCHAR(100),
          AMOUNT INT ,
          REMARK VARCHAR(100)
          )
          GO
    
     CREATE TRIGGER ISHAVE ON SHOPPINGCAR
          INSTEAD OF INSERT                    --这里要用instead of 触发器
               AS
               DECLARE @ITEMID VARCHAR(50),
                       @AMOUNT INT,
                       @REMARK VARCHAR(100)
               SELECT  @ITEMID=ITEMID,@AMOUNT=AMOUNT,@REMARK=REMARK FROM INSERTED
               IF EXISTS(SELECT ID FROM SHOPPINGCAR WHERE ITEMID=@ITEMID)
               BEGIN
                    UPDATE SHOPPINGCAR SET AMOUNT=AMOUNT+@AMOUNT
               END
               ELSE
               BEGIN 
                    INSERT SHOPPINGCAR VALUES(@ITEMID,@AMOUNT,@REMARK)
               END
      GO         
      
    --测试数据
       INSERT   SHOPPINGCAR VALUES('编号001商品',1,'GOOD')
       SELECT * FROM SHOPPINGCAR
       INSERT   SHOPPINGCAR VALUES('编号001商品',2,'GOOD')
       SELECT * FROM SHOPPINGCAR
    -- 测试结果:
    --1    编号001商品    3    GOOD

     应用场景四

    --订单和库存的关系,
    --在向订单中添加商品信息时,先检查库存中是否有货,
    --当添加到订单中后,库存的商品数量要相应的减少

    整理中.......

  • 相关阅读:
    PC版优酷的一次异常
    颜宁开讲啦谈理性思考
    李彦宏开讲啦谈判断能力
    尝试插入cctv视频
    selenium中quit与close方法的区别
    CodeForces 131C The World is a Theatre(组合数)
    CodeForces 446A DZY Loves Sequences(dp)
    UVA 1631 Locker(密码锁)(dp记忆化搜索)
    UVA 1630 Folding(串折叠)(dp记忆化搜索)
    UVA 1629 Cake slicing(切蛋糕)(dp记忆化搜索)
  • 原文地址:https://www.cnblogs.com/mc67/p/4825423.html
Copyright © 2020-2023  润新知