Sql Server触发器

  1、触发器。

  定义： 何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。

 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器，这里不提）

 

 我为什么要使用触发器？

    比如，这么两个表：

 Create Table Student(          --创建学生表

    StudentID int primary key,     --学号，定义主键约束

    ....

    )

 

 Create Table BorrowRecord(          --创建借书记录表

    BorrowRecord int identity(1,1),     --自动增长流水号

    StudentID   int ,             --学号

    BorrowDate   datetime,           --借出时间

    ReturnDAte   Datetime,           --归还时间

    ...

 )

 

 用到的功能有:

    1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);

    2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。

 等等。

 

 这时候可以用到触发器。对于1，创建一个Update触发器：

 

 Create Trigger truStudent

    On Student

    for Update   --触发器类型--Update

 -------------------------------------------------------

 --Name:truStudent

 --func:更新BorrowRecord 的StudentID，与Student同步。

 --Use :None

 --User:System

 --Author: wp_love

 --Date : 2003-4-16

 --Memo : 临时写写的，给大家作个Sample。没有调试阿。

 -------------------------------------------------------

 As

    if Update(StudentID)

    begin

 

    Update BorrowRecord

      Set br.StudentID=i.StudentID

      From BorrowRecord br , Deleted d ,Inserted i

      Where br.StudentID=d.StudentID

 

    end    

         

 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。

 一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

 

 对于2，创建一个Delete触发器

 Create trigger trdStudent

      On Student

      for Delete

 -------------------------------------------------------

 --Name:trdStudent

 --func:同时删除 BorrowRecord 的数据

 --Use :None

 --User:System

 --Author:

 --Date : 2003-4-16

 --Memo : 临时写写的，给大家作个Sample。没有调试阿。

 -------------------------------------------------------

 As

    Delete BorrowRecord

     From BorrowRecord br , Delted d

    Where br.StudentID=d.StudentID

 

 从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。

 这里我们只讲解最简单的触发器。复杂的容后说明

 

 

 

 

关于触发器的学习

 

触发器，有很多的功能，例如，在许多应用中，需要保存某个数据表的历史记录，以供以后查询，在 可

 

以利用触发器轻松实现历史记录功能。

这里学习触发器主要是，想要实现数据库的同步

假设两个数据库，一个为主，一个为备，主数据库的数据发生变化，应该立刻通知备份数据库，进行更

 

新，触发器应该是一种解决办法。

这里以SQL Server触发器为例

介绍一下在SQL Server中触发器的原理

 

触发器的定义从字面上看应该说明的很清楚了，就是我们可以定义并使用触发器这个工具，来响应数据

 

库发生的变化，触发相应的触发器所定义的事件。

 

在SQL Server中的两个虚拟的表 deleted，inserted

具体在实现触发过程中什么时候用上了这两个表，在执行 sql

 

 命令时，这两个虚拟表分别记录的内容如下

 

 sql命令        deleted               inserted

 —————————————————

 insert         [不可用]              新插入的记录

 update      被更新前的记录          被更新后的记录

 delete       被删除的记录              [不可用]

 —————————————————

 

 下面通过一个例子来演示具体的实现方法。

 

 例如：

 

 当前有一个数据表 table1, 有 id, name 两个字段。

 

 现在我们需要在 table1 发生变化（插入，修改，删除）时，记录下每次改动的情况。

 

 这只需要通过如下两个步骤就可以实现：

 

 

 1） 创建一个新表 table1_log, 用于记录 table1 的历史数据。

 

 table1_log 有如下字段：

 

 id, name, sqlcomm (varchar 10), exectime (datetime)

 

 这比 table1 多了 sqlcomm, exectime 两个字段，分别记录 sql 语句的命令（只记录操作的类型

 

insert, update, delete）和命令执行的时间

 

 2） 在表 table1 上增加触发器，以在 table1 发生变动时，自动写入 table1_log.

 

 

 — 针对 insert 命令，增加名为 trTable1_I 的触发器：

 

 ——————————– trTable1_I —————————-

 IF EXISTS (SELECT * FROM dbo.sysobjects

 WHERE id = object_id(N’[trTable1_I]’)

 AND OBJECTPROPERTY(id, N’IsTrigger’) = 1)

 DROP TRIGGER [trTable1_I]

 GO

 

 CREATE TRIGGER trTable1_I

 ON table1

 AFTER insert

 AS

 

 if @@rowcount = 0 –如果影响的行数为 0，则结束触发器运行，避免占用资源

      return

  

 insert into table1_log (id, name, sqlcomm, exectime)

 select id, name， ‘insert’, {fn NOW()} from inserted

 

 go

 ——————————– end trTable1_I ———————–

 

 

 — 针对 update 命令，增加名为 trTable1_U 的触发器：

 

 ——————————– trTable1_U —————————-

 IF EXISTS (SELECT * FROM dbo.sysobjects

 WHERE id = object_id(N’[trTable1_U]’)

 AND OBJECTPROPERTY(id, N’IsTrigger’) = 1)

 DROP TRIGGER [trTable1_U]

 GO

 

 CREATE TRIGGER trTable1_U

 ON table1

 AFTER update

 AS

 

 if @@rowcount = 0 –如果影响的行数为 0，则结束触发器运行，避免占用资源

      return

  

 insert into table1_log (id, name, sqlcomm, exectime)

 select id, name, ‘update’, {fn NOW()} from inserted

 

 go

 ——————————– end trTable1_U ———————–

 

 

 — 针对 delete 命令，增加名为 trTable1_D 的触发器：

 

 ——————————– trTable1_D —————————-

 IF EXISTS (SELECT * FROM dbo.sysobjects

 WHERE id = object_id(N’[trTable1_D]’)

 AND OBJECTPROPERTY(id, N’IsTrigger’) = 1)

 DROP TRIGGER [trTable1_D]

 GO

 

 CREATE TRIGGER trTable1_D

 ON table1

 AFTER delete

 AS

 

 if @@rowcount = 0 –如果影响的行数为 0，则结束触发器运行，避免占用资源

      return

  

 insert into table1_log (id, name, sqlcomm, exectime)

 select id, name, ‘delete’, {fn NOW()} from deleted

 

 go

 ——————————– end trTable1_D ———————–

 

 

 

 在查询分析器中运行以上代码，以后 table1 发生的变化都会记录在 table1_log 表中了。