触发器

 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
    -------------------------------------------------------
    --Name:truStudent
    --func:更新BorrowRecord 的StudentID，与Student同步。
    --Use :None
    --User:System
    --Author:  懒虫 # SapphireStudio ([url]www.chair3.com[/url])
    --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:  懒虫 # SapphireStudio ([url]www.chair3.com[/url])
    --Date  :  2003-4-16
    --Memo  :  临时写写的，给大家作个Sample。没有调试阿。
    -------------------------------------------------------
    As
      Delete BorrowRecord 
        From BorrowRecord br , Delted d
        Where br.StudentID=d.StudentID

    从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。
    这里我们只讲解最简单的触发器。复杂的容后说明。
    事实上，我不鼓励使用触发器。触发器的初始设计思想，已经被“级联”所替代。

  2.游标
   
    在SQL 2000之前，游标可谓是SQL Server心中的痛： 老牛般的速度（CPU），河马般的胃口（内存）。可你却不能不用他。
    什么叫游标呢？说白了就是像高级语言一样，是存放数据集，并逐条访问的一种机制。
    比如在Delphi里面，要实现类似于这样的功能：（呵呵，不好意思，我只会Delphi，所以只能举一个Delphi的例子）
   
    //这是一段Delphi的源代码
    adoDataSet1.Close;
    adoDataSet1.CommandText:=' Select * From Student order by StudentID ';
    adoDataSet1.Open;
    While Not adoDAtaSet1.Eof Do
    Begin
    
      YourVar:=adoDAtaSet1.FieldByName('StudentID').AsInteger;
      DoSomeThing();
      ....  
      adoDataSet1.Next;
    End
     
    在SQL Server 并没有很好的数据逐条访问机制，如果有，那就是游标。

    还是举例子：

    对于表

     Create Table BorrowRecord(              --学生借书记录表
       BorrowRecord  int identity(1,1),      --流水号  
       StudentID     int ,                   --学号
       StudentFeeID  int ,                   --费用结算号   （外键）
       BorrowDate    datetime,               --借出时间
       ReturnDAte    Datetime,               --归还时间
       Fee           Money                   --借书费用 
       ...
     )

     Create Table StudentFee(                --学生费用结算表
       StudentFeeID  int  primarykey ,       --费用结算号   （主键）
       StudentID int ,                       --学号
       BorrowBookAllFee           Money,     --所有借书总费用   
       ...
     )

     两者关系为多对一的关系，关联字段为StudentFeeID

     由于某种原因StudentFee表的数据遭到了破坏，我想StudentFee循环一遍将“所有借书总费用”重算 。

    -----------------------------------------------------------------------
    -------------------------------------------------------
    --Name:一部分代码
    --func:更新学生借书总费用
    --Use :
    --User:
    --Author:  懒虫 # SapphireStudio ([url]www.chair3.com[/url])
    --Date  :  2003-4-16
    --Memo  :  临时写写的，给大家作个Sample。没有调试阿。
    -------------------------------------------------------

     --声明一个游标
     Declare curStudentFee Cursor
       for 
       Select StudentFeeID From StudentFee      

     --声明两个费用变量
     Declare @mBorrowBookAllFee Money  --总费用
     Declare @iStudentFeeID     Int    --借书结算号

     --初始化  
     Set @mBorrowBookAllFee=0
     Set @iStudentFeeID=0

     --打开游标
     Open curStudentFee  

     --循环并提取记录
     Fetch Next From curStudentFee Into @iStudentFeeID     
     While ( @@Fetch_Status=0 )    
     begin

       --从借书记录中计算某一学生的借书总记录的总费用
       Select @mBorrowBookAllFee=Sum(BorrowBookAllFee)
         From BorrowRecord 
         Where StudentFeeID=@iStudentFeeID     

       --更新到汇总表。
       Update StudentFee Set BorrowBookAllFee=@mBorrowBookAllFee
         Where StudentFeeID=@iStudnetFeeID           

       Fetch Next From curStudentFee Into @mFee
     end

     --关闭游标    
     Close curStudentFee

     --释放游标
     Deallocate curStudentFee 

   -----------------------------------------------------------------------
    关注游标的要点：1、声明、打开、关闭、释放 ； 2、@@Fetch_Status 游标提取状态标志，0表示正确

    这里，我也要提到，我不鼓励使用游标。更多的情况下，在SQL 2000 里面 ，函数已经能够实现绝大部分游标的功能。
用触发器实现用户登录记录下登录信息(sysprocesses)
如果你非要用触发器，不妨看看select * from sysprocesses的内容。可以给sysprocesses表加个触发器来完成你的要求。如果你用7.0，可以看看Microsoft提供的sp_who的过程！如下：
create procedure sp_who  --- 1995/11/28 15:48
       @loginame sysname = NULL --or 'active'
as

declare     @spidlow    int,
         @spidhigh    int,
         @spid        int,
         @sid        varbinary(85)

select     @spidlow    =     0
        ,@spidhigh    = 32767

if (    @loginame is not NULL
   AND    upper(@loginame) = 'ACTIVE'
   )
    begin

    select spid ,status
              ,loginame=rtrim(loginame)
          ,hostname ,blk=convert(char(5),blocked)
          ,dbname= db_name(dbid),cmd
    from  master.dbo.sysprocesses
    where spid >= @spidlow and spid <= @spidhigh AND
          upper(cmd) <> 'AWAITING COMMAND'

    return (0)
    end

if (@loginame is not NULL
   AND    upper(@loginame) <> 'ACTIVE'
   )
begin
    if (@loginame like '[0-9]%')    -- is a spid.
    begin
        select @spid = convert(int, @loginame)
        select spid, status,
               loginame=rtrim(loginame),
               hostname,blk = convert(char(5),blocked),
               dbname=db_name(dbid),
               cmd
        from  master.dbo.sysprocesses
        where spid = @spid
    end
    else
    begin
        select @sid = suser_sid(@loginame)
        if (@sid is null)
        begin
            raiserror(15007,-1,-1,@loginame)
            return (1)
        end        
        select spid, status,
               loginame=rtrim(loginame),
               hostname ,blk=convert(char(5),blocked),
               dbname=db_name(dbid),
               cmd
        from  master.dbo.sysprocesses
        where sid = @sid
    end
    return (0)
end

/* loginame arg is null */
select spid,
       status,
       loginame=rtrim(loginame),
       hostname,
       blk=convert(char(5),blocked),
       dbname=db_name(dbid),
       cmd
from  master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh

return (0) -- sp_who

1.如果用dbgrid形式编辑，用触发器限制比较好，系统能够提示用中文显示
CREATE TRIGGER [TRGJCDNAME] ON dbo.TABLEJCD 
FOR INSERT, UPDATE
AS
   DECLARE 
   @CODE VARCHAR(50),
   @XS FLOAT,
   @ROWCOUNT INT
  --检查代码是否为空
  
  //只支持1条操作，如果有多条操作则跳过
  IF @@ROWCOUNT>1 RETURN

  SELECT @CODE=LTRIM(ISNULL(CODE,'''')) FROM INSERTED
  IF @CODE='''' 
  BEGIN
     ROLLBACK TRAN
     RAISERROR(''代码不允许为空或空格!'',16,1)
  END
  --检查代码是否有重复的
  SELECT @ROWCOUNT=COUNT(*) FROM TABLEJCD,INSERTED
  WHERE UPPER(TABLEJCD.CODE)=UPPER(INSERTED.CODE) AND (INSERTED.SYSTEMID=TABLEJCD.SYSTEMID)
  --如果有重复
  IF @ROWCOUNT>1 
  BEGIN
      ROLLBACK TRAN
      RAISERROR(''代码重复,请修改!'',16,1)
  END
  --不允许为0或负数
  SELECT @XS=ISNULL(XS,0) FROM INSERTED
  IF @XS<=0 
  BEGIN
      ROLLBACK TRAN
      RAISERROR(''不允许为0或负数!'',16,1)
  END
2.当操作多条记录时（及联删除或更新）
create trigger del_id on tablename for delete 
as 
  delete from tablename where id in(select id from deleted)
3.递规触发器
  create tablebudget
 (
  dept_name varchar(30) not null,
  part_name varchar(30) null,
  budget_amt money not null)
在上面的表，记录之间是关联，比如部门a,隶属于部门b，顶级部门的父部门为空，我们想到，子部门的预算更新时，其对应的父部门的预算，也要更新

create trigger update_budget
on budget for update as

if (@@rowcount>1)
begin
  print ''only on row can be update at a time''
  rollback tran
  return
end
if (select parent_name from inserted ) is null return
update
  set budget_amt=budget_amt+(select budget_amt from inserted)-
                     (select budget_amt from deleted)
where dept_name=(select parent_name from inserted)

更新时，其对应的父部门
使用嵌套触发器
如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器……这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制是否可以通过"嵌套触发器"服务器配置选项进行触发器嵌套。

如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，则超出嵌套级，而且触发器将终止。

可使用嵌套触发器执行一些有用的日常工作，如保存前一触发器所影响行的一个备份。例如，可以在 titleauthor 上创建一个触发器，以保存由 delcascadetrig 触发器所删除的 titleauthor 行的备份。在使用 delcascadetrig 时，从 titles 中删除title_id PS2091 将删除 titleauthor 中相应的一行或多行。要保存数据，可在 titleauthor 上创建 DELETE 触发器，该触发器的作用是将被删除的数据保存到另一个单独创建的名为 del_save 表中。例如：

CREATE TRIGGER savedel
  ON titleauthor
FOR DELETE
AS
  INSERT del_save
  SELECT * FROM deleted

不推荐按依赖于顺序的序列使用嵌套触发器。应使用单独的触发器层叠数据修改。



说明  由于触发器在事务中执行，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据修改都将回滚。在触发器中包含 PRINT 语句，用以确定错误发生的位置。


递归触发器
触发器不会以递归方式自行调用，除非设置了 RECURSIVE_TRIGGERS 数据库选项。有两种不同的递归方式：

直接递归
即触发器激发并执行一个操作，而该操作又使同一个触发器再次激发。例如，一应用程序更新了表 T3，从而引发触发器 Trig3。Trig3 再次更新表 T3，使触发器 Trig3 再次被引发。

间接递归
即触发器激发并执行一个操作，而该操作又使另一个表中的某个触发器激发。第二个触发器使原始表得到更新，从而再次引发第一个触发器。例如，一应用程序更新了表 T1，并引发触发器 Trig1。Trig1 更新表 T2，从而使触发器 Trig2 被引发。Trig2 转而更新表 T1，从而使 Trig1 再次被引发。

当将 RECURSIVE_TRIGGERS 数据库选项设置为 OFF 时，仅防止直接递归。若要也禁用间接递归，请将 nested triggers 服务器选项设置为 0。

示例
A. 使用递归触发器解决自引用关系
递归触发器的一种用法是用于带有自引用关系的表（亦称为传递闭包）。例如，表 emp_mgr 定义了：

一个公司的雇员 (emp)。


每个雇员的经理 (mgr)。


组织树中向每个经理汇报的雇员总数 (NoOfReports)。
递归 UPDATE 触发器在插入新雇员记录的情况下可以使 NoOfReports 列保持最新。INSERT 触发器更新经理记录的 NoOfReports 列，而该操作递归更新管理层向上其它记录的 NoOfReports 列。

USE pubs
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE pubs
  SET RECURSIVE_TRIGGERS ON
GO
CREATE TABLE emp_mgr (
  emp char(30) PRIMARY KEY,
   mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
   NoOfReports int DEFAULT 0
)
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
  SELECT emp_mgr.emp
  FROM   emp_mgr, inserted
  WHERE emp_mgr.emp = inserted.mgr

OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
  WHERE emp_mgr.emp = @e                            -- added employee.

  FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
--   1. Only singleton updates on emp_mgr.
--   2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
  FROM inserted                            -- (no. of reports) by
  WHERE emp_mgr.emp = inserted.mgr         -- 1 for the new report.

  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
  FROM deleted                             -- (no. of reports) by 1
  WHERE emp_mgr.emp = deleted.mgr          -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave'
GO
SELECT * FROM emp_mgr
GO

以下是更新前的结果：

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Joe                            0
Harry                          NULL                           1
Joe                            Alice                          1
Paul                           Alice                          0

以下为更新后的结果：

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Harry                          0
Harry                          NULL                           2
Joe                            Alice                          0
Paul                           Alice                          0