在SQL Server数据库中,如果您想要查看某张表的修改内容记录,通常有以下几种方法:
1.使用触发器
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Audit]
ON [dbo].[app_fd_M07_SourceList]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TableName NVARCHAR(128) = 'app_fd_M07_SourceList';
DECLARE @AuditAction CHAR(10);
DECLARE @ColName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @sqlinsert NVARCHAR(MAX);
DECLARE @sqldelete NVARCHAR(MAX);
DECLARE column_cursor CURSOR FOR
select name from sys.columns where object_id=OBJECT_ID('app_fd_M07_SourceList')
and name not in ('id','dateCreated','dateModified','createdBy','createdByName','modifiedBy','modifiedByName')
SET @AuditAction = 'Insert'; -- 默认为 'Insert'
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @AuditAction = 'Update'; -- 更新
ELSE IF EXISTS (SELECT * FROM deleted)
SET @AuditAction = 'Delete'; -- 删除
IF OBJECT_ID('stg_insertd') is not null
DROP TABLE stg_insertd
IF OBJECT_ID('stg_deleted') is not null
drop TABLE stg_deleted
set @sqlinsert='select * into stg_insertd from '+@TableName+' where 1=2'
set @sqldelete='select * into stg_deleted from '+@TableName+' where 1=2'
EXEC sp_executesql @sqlinsert
EXEC sp_executesql @sqldelete
IF EXISTS (SELECT * FROM inserted)
insert into stg_insertd
select * from inserted
IF EXISTS (SELECT * FROM deleted)
insert into stg_deleted
select * from deleted
OPEN column_cursor;
FETCH NEXT FROM column_cursor INTO @ColName;
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=N'INSERT INTO [app_fd_M07_Event] (Updatetable, AuditAction, ColumnName, OldValue, NewValue, ModifiedDate, ModifiedBy)
SELECT
''' + @TableName + ''',
''' + @AuditAction + ''',
''' + @ColName + ''',
d.' + QUOTENAME(@ColName) + ',
i.' + QUOTENAME(@ColName) + ',
GETDATE(),
SUSER_SNAME() -- 当前用户
FROM
stg_insertd i
FULL OUTER JOIN stg_deleted d ON i.id = d.id
WHERE
(i.' + QUOTENAME(@ColName) + ' IS NOT NULL AND d.' + QUOTENAME(@ColName) + ' IS NOT NULL AND i.' + QUOTENAME(@ColName) + ' <> d.' + QUOTENAME(@ColName) + ')
OR i.' + QUOTENAME(@ColName) + ' IS NULL
OR d.' + QUOTENAME(@ColName) + ' IS NULL'
-- 插入变更记录到审计表
-- )
-- print @sql
EXEC sp_executesql @sql
FETCH NEXT FROM column_cursor INTO @ColName;
END
CLOSE column_cursor;
DEALLOCATE column_cursor;
END
2.使用变更数据捕获(CDC): SQL Server的变更数据捕获功能可以捕获表中的插入、更新和删除操作,并将这些变更保存在一组特定的表中,可以使用系统存储过程来查询这些变更。
启用CDC的步骤如下:
在数据库上启用CDC:
EXEC sys.sp_cdc_enable_db
在特定表上启用CDC:
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'YourTableName',
@role_name = NULL,
@supports_net_changes = 1
查询变更的数据:
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTableName(
@from_lsn = sys.fn_cdc_get_min_lsn('dbo_YourTableName'),
@to_lsn = sys.fn_cdc_get_max_lsn(),
@row_filter_option = 'all'
)
SQL Server审计: SQL Server审计功能可以跟踪数据库中指定的操作,并记录到审计文件或表中。启用审计的步骤较为复杂,需要通过SQL Server Management Studio (SSMS) 或 T-SQL 命令来配置审计策略。
事务日志: 如果上述方法都没有使用,并且事务日志未被截断,你可以查询事务日志来查看更改。但是,这通常需要DBA权限,并且涉及使用DBCC LOG 和其他高级技术,通常不推荐用于常规操作。
使用上述任何一种方法之前,请确保你有足够的权限来执行所需的操作,并且考虑到对性能的影响,尤其是在生产环境中。如果是为了合规性目的,建议使用SQL Server审计功能,因为它提供了详细的审核跟踪和报告功能。