快捷搜索:

SQL Server 2008中新增的变更数据捕获(CDC)和更改

本文主要先容SQL Server中记录数据变化的四个措施:触发器、Output子句、变化数据捕获(Change Data Capture 即CDC)功能、同步变动跟踪。此中后两个为SQL Server 2008所新增。

一、触发器

在SQL Server的早期版本中,假如要记录某个表或视图的Insert/Update/Delete操作,我们可以借助触发器(Trigger)(http://msdn.microsoft.com/zh-cn/library/ms189799.aspx),这在数据量较小的环境下每每是有效的要领之一,此中后触发器(After Trigger)只能跟踪表的三个操作中的随意率性组合,而前触发器(Instead Of trigger)可以处置惩罚表和视图的更新(纵然通俗的Update View语句在某些列不明确的环境下报错)。我们看两个例子:

筹备根基数据:

USE testDb2

GO

--创建两个测试表

IF NOT OBJECT_ID('DepartDemo') IS NULL

DROP TABLE [DepartDemo]

GO

IF NOT OBJECT_ID('DepartChangeLogs') IS NULL

DROP TABLE [DepartChangeLogs]

GO

--测试表

CREATE TABLE [dbo].[DepartDemo](

[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,

[DName] [nvarchar](200) NULL,

[DCode] [nvarchar](500) NULL,

[Manager] [nvarchar](50) NULL,

[ParentID] [int] NOT NULL DEFAULT ((0)),

[AddUser] [nvarchar](50) NULL,

[AddTime] [datetime] NULL,

[ModUser] [nvarchar](50) NULL,

[ModTime] [datetime] NULL,

[CurState] [smallint] NOT NULL DEFAULT ((0)),

[Remark] [nvarchar](500) NULL,

[F1] [int] NOT NULL DEFAULT ((0)),

[F2] [nvarchar](300) NULL

)

GO

--记录日志表

CREATE TABLE [DepartChangeLogs]

([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY,

[DID] [int] NOT NULL,

[DName] [nvarchar](200) NULL,

[DCode] [nvarchar](500) NULL,

[Manager] [nvarchar](50) NULL,

[ParentID] [int] NOT NULL DEFAULT ((0)),

[AddUser] [nvarchar](50) NULL,

[AddTime] [datetime] NULL,

[ModUser] [nvarchar](50) NULL,

[ModTime] [datetime] NULL,

[CurState] [smallint] NOT NULL DEFAULT ((0)),

[Remark] [nvarchar](500) NULL,

[F1] [int] NOT NULL DEFAULT ((0)),

[F2] [nvarchar](300) NULL,

[LogTime] DateTime Default(Getdate()) Not Null,

[InsOrUpd] char not null

)

GO

创建触发器:

/奸淫奸淫*   创建一个After DML触发器  奸淫奸淫/

/奸淫奸淫奸淫3w@live.cn邀月奸淫奸淫奸淫奸淫奸淫/

CREATE TRIGGER dbo.tri_LogDepartDemo

ON [dbo].[DepartDemo]

AFTER INSERT, Delete/奸淫奸淫奸淫奸淫此处应用update与“Insert,Delete”效果是一样的,邀月注奸淫奸淫奸淫*/

AS

SET NOCOUNT ON--樊篱触发器发送“受影响的行数”给利用法度榜样

-- Inserted rows

INSERT [DepartChangeLogs]

(DID,[DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],

LogTime, InsOrUPD)

SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],

GETDATE(), 'I'

FROM inserted i

-- Deleted rows

INSERT [DepartChangeLogs]

(DID,[DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],

LogTime, InsOrUPD)

SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],

GETDATE(), 'D'

FROM deleted d

GO

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])

VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser',

CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),

1, N'专业评估全国房价,为老庶夷易近谋福祉', 0, N'')

GO

----该Update不会被触发器记录,但Update会生效

UPDATE departDemo SET [Manager]='任我行' WHERE DID=101

GO

DELETE FROM departDemo where DID=101

GO

SELECT * FROM [DepartChangeLogs]

统计效果:

假如你感觉触发器过于挥霍,你可以试着根据某些字段以缩小触发器的范围

/奸淫奸淫奸淫 应用DML触发器记录特定列的改动 奸淫/

/奸淫奸淫奸淫3w@live.cn邀月奸淫奸淫奸淫奸淫奸淫/

CREATE TRIGGER dbo.[tri_LogDepartDemo2]

ON [dbo].[DepartDemo]

AFTER Update

AS

IF Update([Manager])

Begin

print '该部门主管推行终生任免制,不得半途变动!'

Rollback ----回滚Update操作

End

GO

UPDATE departDemo SET [Manager]='任我行' WHERE DID=101

GO

履行结果:

但触发器的缺陷也是显而易见的,应用触发器请留意以下几点:

1、触发器平日很隐蔽,换句话说,易忘怀,分外在反省机能和逻辑问题时。

2、长光阴运行的触发器会严重减慢数据操作,分外是在数据频繁改动的数据库中。

3、不记录日志的更新不会引起DML触发器的触发(如WRITETEXT、Trunacte table及批量插入操作)。

4、约束平日比触发器运行更快。

5、处置惩罚某些逻辑时,存储历程平日比触发器要更易掩护和治理。

6、不容许在触发器中应用Select返回结果集。

关于触发器的更多内容,请看MSDN(http://msdn.microsoft.com/zh-cn/library/ms189799.aspx)

二、应用Output子句

官方解释:OutPut子句(http://technet.microsoft.com/zh-cn/library/ms177564.aspx)返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处置惩罚利用法度榜样,以供在确认消息、存档以及其他类似的利用法度榜样要求中应用。 也可以将这些结果插入表或表变量。 别的,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。

举例:

/奸淫奸淫奸淫 应用Output记录表记录的改动 奸淫**/

/奸淫奸淫奸淫3w@live.cn邀月奸淫奸淫奸淫奸淫奸淫/

----删除前面的触发器

Drop TRIGGER dbo.[tri_LogDepartDemo]

DROP TRIGGER dbo.[tri_LogDepartDemo2]

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],

[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])

OUTPUT Inserted.*,getdate(),'I' ---留意这行是新增的

INTO DepartChangeLogs---留意这行是新增的

VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser',

CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),

1, N'油价,我说了算', 0, N'')

GO

SELECT * FROM [DepartChangeLogs]

留意:

1、从OUTPUT 中返回的列反应 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器履行之前的数据。

2、SQL Server 并不包管由应用 OUTPUT 子句的 DML 语句处置惩罚和返回行的顺序。

3、与触发器比拟,OutPut子句可以直接处置惩罚Merge语句。

以上两种措施各有所长,在相宜的环境下采取相宜的措施才是明智的选择,令人惊喜的是,SQL Server 2008起,为我们供给了更为强大年夜的内建的措施-变化数据捕获(CDC,http://msdn.microsoft.com/zh-cn/library/bb500244%28v=sql.100%29.aspx)和变动跟踪,下面我们隆重先容它们。

三、应用“变化数据捕获”(CDC)功能

SQL Server 2008供给了内建的措施变化数据捕获(Change Data Capture 即CDC)以实现异步跟踪用户表的数据改动,而且这一功能拥有最小的机能开销。可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变化迁移到数据仓库数据库。

要应用CDC功能,首先我们得在数据库中启用该功能。在此我们沿用上例中应用的数据库Testdb2

/奸淫奸淫奸淫奸淫**异步跟踪数据更新演示奸淫奸淫奸淫奸淫*/

/奸淫奸淫奸淫奸淫* 3w@live.cn 邀月奸淫奸淫奸淫奸淫奸淫/

use master

GO

IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb2')

drop DATABASE TestDb2

Go

CREATE DATABASE TestDb2

GO

--查看是否启用CDC

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2'

USE TestDb2

GO

----启用当前数据库的CDC功能

EXEC sys.sp_cdc_enable_db

GO

您可能还会对下面的文章感兴趣: