时间戳

首页 > 技术文章 > SQL2008触发器

SQL2008触发器

Lixinhua-GoOn 2014-06-10 12:42 原文

最近第一次接触触发器，感觉很是新奇，也很是蛋疼，因为老板要求的是在触发器中获取用户信息，并把对表的操作进行记录，后者实现到时比较简单，前者确实让我纠结了好久，其实百度了一下关于SQL2008触发器的文章还是挺多的，写的好的也有好几篇，不过我觉得还是要根据自己的理解再给广大网友分享一下，从初识、理解、到最后的使用。

首先来看一下触发器的语法：

USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建或修改（CREATE|ALTER） TRIGGER 触发器名（最好以XXXX_TR命名规范结尾） 
ON
表名
FOR 对于什么操作触发（Insert,Update,Delete ）
AS
BEGIN
    触发后要做的操作
end

讲到触发器，当然少不了要提一下Inserted和Deleted表，

Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：                             

　　　　　　　　　　 虚拟表Inserted                     虚拟表Deleted

在表记录新增时       存放新增的记录                         不存储记录         

修改时           　　 存放用来更新的新记录                   存放更新前的记录         

删除时           　　 不存储记录                             存放被删除的记录

一个Insert 的过程可以看作为：生成的记录到Inserted表，

一个Delete的过程可以看作为：生成的记录到Deleted表，

一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表

这两个表在触发器中很是重要，一定要理解和区分 他们的关系和作用！

这两个表的结构和触发器对应的表结构是相同的！

在贴出我的关于删除触发的触发器实例供大家参考：

 1 USE [pd]
 2 GO
 3 /****** Object:  Trigger [dbo].[table_delete]    Script Date: 06/10/2014 09:39:20 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 create TRIGGER [dbo].[DayRunRecord20140528_delete]
 9    ON [dbo].[DayRunRecord20140528]
10    after delete
11 AS 
12 if @@rowcount = 0 --如果影响的行数为 0，则结束触发器运行，避免占用资源
13 begin
14 return
15 end
16 else
17 BEGIN
18  SET NOCOUNT ON;
19  DECLARE @OperateDate datetime;
20  DECLARE @IPAddreass VARCHAR(20);
21  DECLARE @HostName varchar(50);
22  DECLARE @loginame varchar(50);
23  DECLARE @content varchar(5000)
24  DECLARE @content_temp varchar(2000)
25  DECLARE @OperateTable varchar(50);
26     --获取用户新增内容(人员定位表的字段)
27     DECLARE @ID int;
28     DECLARE @MineNumber int;
29     DECLARE @PepoleNumber int;
30     DECLARE @RouteStation varchar(10);
31     DECLARE @RouteTime datetime;
32     DECLARE @DataFlag int;
33     DECLARE @Remark1 char(50);
34     DECLARE @WorkNumber varchar(50);
35     DECLARE @ReceiveTime datetime;
36     DECLARE @EnterFlag int;
37     DECLARE @PowerFlag int;
38     DECLARE @DeviceDbId int;
39     DECLARE @TR_LastUpdateTime datetime;
40     DECLARE @TR_GUID uniqueidentifier;
41     set @content = '';
42     --声明游标
43     declare myCursor Cursor for
44     SELECT * from deleted
45     open myCursor 
46     fetch next from myCursor into @ID,@MineNumber,@PepoleNumber,@RouteStation,@RouteTime,@DataFlag,@Remark1,@WorkNumber,@ReceiveTime,@EnterFlag,@PowerFlag,@DeviceDbId,@TR_LastUpdateTime,@TR_GUID
47     WHILE (@@FETCH_STATUS = 0)
48     BEGIN
49     set @content_temp = 'ID='+Convert(varchar,@ID) + 
50     ';MineNumber='+Convert(varchar,@MineNumber)+
51     ';PepoleNumber='+Convert(varchar,@PepoleNumber)+
52     ';RouteStation='+ @RouteStation +
53     ';RouteTime='+Convert(varchar,@RouteTime)+
54     ';DataFlag='+Convert(varchar,@DataFlag)+
55     ';Remark1='+Convert(varchar,@Remark1) +
56     ';WorkNumber='+@WorkNumber+
57     ';ReceiveTime'+Convert(varchar,@ReceiveTime)+
58     ';EnterFlag='+Convert(varchar,@EnterFlag)+
59     ';PowerFlag='+Convert(varchar,@PowerFlag)+
60     ';DeviceDbId='+Convert(varchar,@DeviceDbId)+
61     ';TR_LastUpdateTime='+Convert(varchar,@TR_LastUpdateTime)+
62     ';TR_GUID='+CONVERT(varchar(36),@TR_GUID);
63     if(@content<>'')
64     begin
65         set @content = @content + '&' +@content_temp ;
66     end
67     else
68     begin
69         set @content = @content_temp ;
70     end
71         --将游标下移
72         FETCH NEXT FROM myCursor into @ID,@MineNumber,@PepoleNumber,@RouteStation,@RouteTime,@DataFlag,@Remark1,@WorkNumber,@ReceiveTime,@EnterFlag,@PowerFlag,@DeviceDbId,@TR_LastUpdateTime,@TR_GUID
73     END
74     --关闭游标
75     CLOSE myCursor 
76     --释放游标
77     DEALLOCATE myCursor 
78      set @OperateTable = 'DayRunRecord20140528';
79      set @IPAddreass = convert(varchar,CONNECTIONPROPERTY('client_net_address'));
80      set @HostName = CONVERT(varchar,HOST_NAME());
81      set @OperateDate = GETDATE();
82      set @loginame = CONVERT(varchar,(select system_user));
83     --为日志表添加数据
84     insert into XJK_USER_OperateLog(OperateType,OperateTable,OperateContent,OperateDate,IPAddreass,HostName,UserName)
85     values('删除',@OperateTable,@content,@OperateDate,@IPAddreass,@HostName,@loginame);
86 END

其实代码虽然多，里面实现的功能却很简单，就是遍历Deleted表中删除的内容，并把这些内容拼接到一个变量中，用于记录成一个日志。

推荐阅读