触发器的替代-cdc数据变更记录更好的方式


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

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

--开启cdc
EXEC sys.sp_cdc_enable_db

--net_changes说的是针对一行记录,如果有多个更改的话,那么以最后的一条为准。
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='frmuser',@capture_instance='frmuser',@supports_net_changes=0,@role_name=null 

--确认表已经被跟踪  
  
SELECT is_tracked_by_cdc FROM sys.tables  
WHERE name = 'frmuser' and schema_id = SCHEMA_ID('dbo')  

EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'frmuser' 

--更改表数据查询效果
UPDATE a
SET a.isvalid  = 0
FROM frmuser a
WHERE Account ='sa'


SELECT TOP 50 * 
FROM [cdc].[frmuser_CT]

DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2013-09-27 08:30')  
  
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2013-09-27 23:59:59')  
  
--注意若在上面的日期范围内无数据的话,将提示参数不足
SELECT  
CASE __$operation  
WHEN 1 THEN 'DELETE'  
WHEN 2 THEN 'INSERT'  
WHEN 3 THEN 'Before UPDATE'  
WHEN 4 THEN 'After UPDATE'  
END   ,a.*
FROM [cdc].[fn_cdc_get_all_changes_frmuser]  
(@FromLSN, @ToLSN, 'all')   a


--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo', 
'FactInternetSales','All'

EXEC sys.sp_cdc_disable_db

更改是不会永远保存,会定期清除
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行,每个扫描周期最多可处理 1000 个事务,并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天(4320 分钟),可使用单个删除语句最多删除 5000 项。