DML触发器

触发器简介

 

      触发器是一种特殊类型的存储过程。其优点包括：

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

      2. 触发器可以通过数据库中的相关表进行级联更改。

      3. 触发器可以强制限制。

 

      触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用。触发器功能强大，但是过分依赖触发器会造成数据库及应用程序的维护困难，也影响了数据库的结构。

 

DML触发器简介

 

      DML触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。包括After触发器和Instead of触发器。

 

      1. After触发器：在记录已经改变完之后，才会被激活执行，用于记录变更后的处理或检查，发现错误，可以用Rollback Transaction语句来回滚本次操作。不能在视图上定义After触发器。

 

      2. Instead Of 触发器：在记录变更之前发生的，用来取代原本的操作，它并不执行原来SQL语句（Insert,Update,Delete），而去执行触发器本身所定义的操作。Instead Of 触发器不能在 with check option 的可更新视图上定义。如果向指定了 with check option选项的可更新视图添加 Instead Of 触发器，SQL Server 将产生一个错误。用户必须用 alter view 删除该选项后才能定义 Instead Of 触发器。对于 Instead Of 触发器，不允许在具有 on delete 级联操作引用关系的表上使用 delete 选项。同样，也不允许在具有 on update 级联操作引用关系的表上使用 update 选项。

 

DML触发器工作原理

 

      在SQL Server 2005里，存在两个特殊的表：一个是inserted表，一个是deleted表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构完全一致，当触发器的工作完成之后，这两个表也将会从内存中删除。

 

      inserted表：存放的是更新前的记录。对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。

 

      deleted表：存放的是更新后的记录。对于更新记录操作来说，删除表里存放的是更新前的记录；对于删除记录操作来说，删除表里存放的是被删除的旧记录。

 

      不能在 inserted 表和 deleted 表中使用 text、ntext 或 image 列。

 

一.  给一个表创建触发器的格式（同一个数据库下）

     

      create trigger 触发器名称      //触发器名称必须唯一，与触发器在哪个数据表或视图无关

      on 数据表名（或视图名）      //指定触发器所在的数据表或视图，即将要触发操作的表

      for insert [delete] [update]      //触发器的类型，至少要指定一个，若指定多个时，必须用逗号来分开，其顺序可以任意摆放

      as

      begin

            --这里是要运行的Sql语句--

      end

      go

 

      例如：如果GW_ADNM_B中发生删除操作，则把这条删除的信息添加到SX_ADNM_B中

 

      create trigger tr1
      on GW_ADNM_B
      for insert,update,delete
      as
      if exists(select * from deleted)
      begin
      insert into SX_ADNM_B (addvcd,addvnm)select addvcd,addvnm from deleted
      end
      go

 

二.  跨数据库的触发器

USE ComputerWebsite   --数据库1
GO

 

CREATE TRIGGER trigger_selsyn   --触发器名称

ON GW_ADNM_B   --操作表1

FOR INSERT,UPDATE,DELETE    --触发器类型

AS

IF NOT EXISTS (SELECT * FROM deleted)    --INSERT触发器

BEGIN

INSERT ZongHeDataBase..GW_ADNM_B(addvcd,addvnm,area,a) SELECT addvcd,addvnm,area,a FROM inserted

END

ELSE IF NOT EXISTS (SELECT * FROM inserted)--DELETE触发器

BEGIN

DELETE ZongHeDataBase..GW_ADNM_B WHERE addvcd IN (SELECT addvcd FROM deleted)

END

ELSE--UPTATE触发器

BEGIN

UPDATE ZongHeDataBase..GW_ADNM_B SET addvnm=inserted.addvnm FROM inserted WHERE ZongHeDataBase..GW_ADNM_B.addvcd=inserted.addvcd

END
GO

 

 

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝测试部分＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SELECT * FROM ComputerWebsite..GW_ADNM_B
SELECT * FROM ZongHeDataBase..GW_ADNM_B

GO

INSERT INTO ComputerWebsite..GW_ADNM_B VALUES('0001','TestName01','12','')--测试INSERT触发器

SELECT * FROM ComputerWebsite..GW_ADNM_B
SELECT * FROM ZongHeDataBase..GW_ADNM_B
GO

UPDATE ComputerWebsite..GW_ADNM_B set ComputerWebsite..GW_ADNM_B.addvnm='UPDATENAME' WHERE ComputerWebsite..GW_ADNM_B.addvcd='0001'--测试UPDATE触发器

SELECT * FROM ComputerWebsite..GW_ADNM_B
SELECT * FROM ZongHeDataBase..GW_ADNM_B
GO

DELETE ComputerWebsite..GW_ADNM_B where ComputerWebsite..GW_ADNM_B.addvcd='0001'--测试DELETE触发器

SELECT * FROM ComputerWebsite..GW_ADNM_B
SELECT * FROM ZongHeDataBase..GW_ADNM_B
GO

 

三.  跨服务器跨数据库的触发器（待）

 

 

 

最后要注意一点就是：Sql 2000将触发器保存在每张表下面的触发器中；而Sql 2005将触发器保存在可编程性下面的数据库触发器中。