SQL Server 2005中触发器监控数据库变化

sql中如何建立一个触发器，使出入和删除某条记录时，系统能自动进行相关的提示。

4

CREATE TRIGGER [触发器名字] ON 表名 FOR INSERT(插入)|DELETE(删除)|UPDATE(更新) AS declare @username varchar(50)//自定义@母表字段 select @username=UserName from inserted insert 需要更新的表字段(类型) values( @username) 下面例子: CREATE TRIGGER [tgUser] ON dbo.Users FOR DELETE AS declare @username varchar(50) select @username=UserName from inserted insert Score (ID) values( @username) 当我向users表删除username的时候同时在score表删除这个ID

 

数据仓库中捕获变化数据(CDC，Changed Data Capture)的四种手法

在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除。触发器方式的优点是数据抽取的性能较高，缺点是要求业务表建立触发器，对业务系统有一定的影响。

b.时间戳：

它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，即表的其它字段的数据发生改变时，自动更新时间戳字段的值。有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单，但对业务系统也有很大的倾入性(加入额外的时间戳字段)，特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作。另外，无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。

c.全表比对：

典型的全表比对的方式是采用MD5校验码。ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MD5校验码。MD5方式的优点是对源系统的倾入性较小(仅需要建立一个MD5临时表)，但缺点也是显而易见的，与触发器和时间戳方式中的主动通知不同，MD5方式是被动的进行全表数据的比对，性能较差。当表中没有主键或唯一列且含有重复记录时，MD5方式的准确性较差。

d.日志对比：

通过分析数据库自身的日志来判断变化的数据。Oracle的改变数据捕获(CDC，Changed Data Capture)技术是这方面的代表。CDC特性是在Oracle9i数据库中引入的。CDC能够帮助你识别从上次抽取之后发生变化的数据。利用CDC，在对源表进行insert、update或delete等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。CDC体系结构基于发布者/订阅者模型。发布者捕捉变化数据并提供给订阅者。订阅者使用从发布者那里获得的变化数据。通常，CDC系统拥有一个发布者和多个订阅者。发布者首先需要识别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对发布者发布的所有数据都感兴趣。订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。CDC分为同步模式和异步模式，同步模式实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中。异步模式则是基于Oracle的流复制技术。

关键字： DBCC PAGE SQL Server 2005 SQL Server REVERT 聚合函数 
通过创建DDL触发器，记录数据库对象的增删改的操作，能够在数据库出现问题时，便于跟踪、定位问题。本文通过实例介绍了如何使用DDL触发器。 
　　添加，删除或修改数据库的对象，一旦误操作，可能会导致大麻烦，需要一个数据库管理员或开发人员对相关可能受影响的实体进行代码的重写。 

　　为了在数据库结构发生变动而出现问题时，能够跟踪问题，定位问题的根源，我们可以利用DDL触发器来记录类似“用户建立表”这种变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化的繁琐程度。

　　1、DDL触发器介绍

　　DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

　　使用 DDL 触发器，可以达到以下几种目的：

　　要防止对数据库架构进行某些更改。 

　　希望数据库中发生某种情况以响应数据库架构中的更改。 

　　要记录数据库架构中的更改或事件。

　　与标准的DML触发器一样，DDL 触发器在响应事件时执行存储过程。 但与标准的DML触发器不同的是，它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。 它们主要在响应数据定义语言 (DDL) 语句执行存储过程。 这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

　　2、如何使用DDL触发器

　　第一步，需要建立一个表，用来记录数据库范围内所有DDL操作。

　　下面的代码在AdventureWorks范例数据库中创建一个表，用于保存所有DDL操作记录：

　　USEAdventureWorks
　　GO
　　CREATETABLEAuditLog
　　(IDINTPRIMARYKEYIDENTITY(1,1),
　　CommandNVARCHAR(1000),
　　PostTimeNVARCHAR(24),
　　HostNameNVARCHAR(100),
　　LoginNameNVARCHAR(100)
　　)
　　GO

　　用于保存DDL事件的表在创建好之后，还需要建立一个DDL触发器，监控AdventureWorks数据库中DDL_DATABASE_LEVEL_EVENTS级别的所有事件：

　　CREATETRIGGERAuditONDATABASE
　　FORDDL_DATABASE_LEVEL_EVENTS
　　AS
　　DECLARE@dataXML
　　DECLARE@cmdNVARCHAR(1000)
　　DECLARE@posttimeNVARCHAR(24)
　　DECLARE@spidNVARCHAR(6)
　　DECLARE@loginnameNVARCHAR(100)
　　DECLARE@hostnameNVARCHAR(100)
　　SET@data=EVENTDATA()
　　SET@cmd=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(1000)')
　　SET@cmd=LTRIM(RTRIM(REPLACE(@cmd,'','')))
　　SET@posttime=@data.value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(24)')
　　SET@spid=@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(6)')
　　SET@loginname=@data.value('(/EVENT_INSTANCE/LoginName)[1]',
　　'NVARCHAR(100)')
　　SET@hostname=HOST_NAME()
　　INSERTINTOdbo.AuditLog(Command,PostTime,HostName,LoginName)
　　VALUES(@cmd,@posttime,@hostname,@loginname)
　　GO
　　使用 EVENTDATA 函数，可以捕获有关激发 DDL 触发器的事件的信息，并将其保存到我们的AuditLog日志表中。EVENTDATA 函数的是返回 xml 值，采用以下的命令进行解析：
　　SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

　　需要LTRIM和RTRIM是所有的左边的前导空格和右边的尾随空格，而REPLACE函数是用来消除使用SSMS脚本向导时所带来的回车。

　　一旦建立了表和触发器，您就可以用以下的操作，来测试看看DDL触发器是否正常运行：

　　UPDATESTATISTICSProduction.Product
　　GO
　　CREATETABLEdbo.Test(colINT)
　　GO
　　DROPTABLEdbo.Test
　　GO
　　--Viewlogtable
　　SELECT*
　　FROMdbo.AuditLog
　　GO
　　执行后，查询结果如下所示：
　　图1: AuditLog日志表查询结果
　　3、小结
　　通过创建一个日志表来保存所有DDL操作以及创建数据库级别的DDL触发器，我们能够成功地捕获我们的数据库中所有DDL级的变化，为DBA跟踪和监视任何改变提供了更强大的功能。