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

应用场景四

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

整理中.......