SQL Server数据库Table Data Audit

对于重要的数据库表，特别是关于权限的，我们在生产机环境都要存储Audit信息，下面我们先生成一个Audit Table:

CREATE TABLE [dbo].[DATA_AUDIT](
	[ROW_ID] [uniqueidentifier] NOT NULL,
	[LST_CHG_TMS] [datetime] NOT NULL,
	[LST_CHG_USR_ID] [nvarchar](12) NOT NULL,
	[TARGET_TABLE] [nvarchar](128) NOT NULL,
	[ACTION] [nvarchar](50) NULL,
	[RECORD_KEY] [nvarchar](200) NULL,
	[OLD_ROW_DETAILS] [varchar](MAX) NULL,
	[NEW_ROW_DETAILS] [varchar](MAX) NULL
)

有了这张表，对于每一个需要Audit的表，我们需要添加一个Trigger，这个过程比较枯燥，可以通过下面的T4 Template在Visual Studio中实现：

<#@ template debug="false" hostspecific="false" #>
<#@ output extension=".sql" #>
<#@ assembly name="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ assembly name="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ assembly name="System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Transactions" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Linq" #>
<#	var databaseName = "";
	var schemaName = "dbo";
	var tableName = "";

	var connectionString = "";
#>
<#
	var	triggerName = "TRG_" + tableName;
#>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO

USE <#= databaseName #> 
GO

IF NOT EXISTS
(
	SELECT 1
	FROM SYS.Triggers tr
	INNER JOIN SYS.Tables t
	ON tr.parent_id = t.object_id
	WHERE t.name = '<#= tableName #>'
	AND tr.name = '<#= triggerName #>'
)
BEGIN
	DECLARE @sql NVARCHAR(MAX) = '
	CREATE TRIGGER <#= schemaName #>.<#= triggerName #> ON <#= schemaName #>.<#= tableName #>
	FOR DELETE, INSERT, UPDATE
	NOT FOR REPLICATION
	AS
	BEGIN
		SELECT 1
	END'
	EXEC (@sql)
END
GO

ALTER TRIGGER <#= schemaName #>.<#= triggerName #> ON <#= schemaName #>.<#= tableName #>
FOR DELETE, INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
	DECLARE @USER_ID CHAR(8) = 'mcai4gl2',
			@CURR_TIME DATETIME = GETDATE(),
			@COUNT_OLD INT,
			@COUNT_NEW INT

	SELECT @COUNT_OLD = COUNT(1) FROM DELETED
	SELECT @COUNT_NEW = COUNT(1) FROM INSERTED
	<#
		var columns = new Dictionary<String, int>();
		
		using (var conn = new SqlConnection(connectionString))
		{
			conn.Open();

			var cmd = new SqlCommand(
				String.Format(
				@"
					select DISTINCT c.column_name, 
					CASE WHEN OBJECTPROPERTY(OBJECT_ID(k.constraint_name), 'IsPrimaryKey') = 1 THEN 1 ELSE 0 END AS IsPrimary,
					c.ORDINAL_POSITION
					from information_schema.columns c
					LEFT join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
					on c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME
					where c.table_name = '{0}'
					order by c.ORDINAL_POSITION
				", tableName),
				conn);

			using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
					columns.Add(reader.GetString(0), reader.GetInt32(1));
                }
            }
		}

		var keyStrs = (from column in columns where column.Value == 1 select "'<" + column.Key.Trim() + ">' + " 
					  + "ISNULL(CONVERT(VARCHAR(MAX), {0}" + column.Key.Trim() + "), '') + '</" + column.Key.Trim() + ">").ToList();
		var valueStrs = (from column in columns where column.Value == 0 select "'<" + column.Key.Trim() + ">' + " 
					  + "ISNULL(CONVERT(VARCHAR(MAX), {0}" + column.Key.Trim() + "), '') + '</" + column.Key.Trim() + ">").ToList();
	#>

	-- UPDATE
	IF @COUNT_NEW > 0 AND @COUNT_OLD > 0
	BEGIN
		INSERT INTO <#= schemaName #>.DATA_AUDIT
		(
			LST_CHG_TMS,
			LST_CHG_USR_ID,
			TARGET_TABLE,
			ACTION,
			RECORD_KEY,
			OLD_ROW_DETAILS,
			NEW_ROW_DETAILS
		)
		SELECT
			@CURR_TIME,
			RTRIM(@USER_ID),
			'<#= tableName #>',
			'Update',
			ISNULL(RES.KEY_VAL,'UNKNOWN'),
			ISNULL(RES.OLD_XML_OUT,''),
			ISNULL(RES.NEW_XML_OUT,'')	
			FROM
			(
				SELECT
<# 
	for (int index = 0; index < valueStrs.Count(); index++) {
		var line = valueStrs[index];
		if (index < valueStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "d.") #>
<#
	}
#>
					AS OLD_XML_OUT,
<# 
	for (int index = 0; index < valueStrs.Count(); index++) {
		var line = valueStrs[index];
		if (index < valueStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "i.") #>
<#
	}
#>
					AS NEW_XML_OUT,
<# 
	for (int index = 0; index < keyStrs.Count(); index++) {
		var line = keyStrs[index];
		if (index < keyStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "i.") #>
<#
	}
#>
					AS KEY_VAL
				FROM 
					DELETED d
					INNER JOIN INSERTED i ON
<# 
	var keys = (from column in columns where column.Value == 1 select column.Key.Trim()).ToList();
	for (int index = 0; index < keys.Count(); index++) {
		var line = "i." + keys[index] + " = d." + keys[index];
		if (index < keys.Count() - 1)
		{
			line += " AND";
		}
#>
					<#= line #>
<#
	}
#>
			) RES
			WHERE RES.NEW_XML_OUT <> RES.OLD_XML_OUT
	END
	-- INSERT
	ELSE IF @COUNT_NEW > 0 AND @COUNT_OLD = 0
	BEGIN
			INSERT INTO <#= schemaName #>.DATA_AUDIT
		(
			LST_CHG_TMS,
			LST_CHG_USR_ID,
			TARGET_TABLE,
			ACTION,
			RECORD_KEY,
			OLD_ROW_DETAILS,
			NEW_ROW_DETAILS
		)
		SELECT
			@CURR_TIME,
			RTRIM(@USER_ID),
			'<#= tableName #>',
			'Insert',
			ISNULL(RES.KEY_VAL,'UNKNOWN'),
			'',
			ISNULL(RES.NEW_XML_OUT,'')
			FROM
			(
				SELECT
<# 
	for (int index = 0; index < valueStrs.Count(); index++) {
		var line = valueStrs[index];
		if (index < valueStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "i.") #>
<#
	}
#>
					AS NEW_XML_OUT,
<# 
	for (int index = 0; index < keyStrs.Count(); index++) {
		var line = keyStrs[index];
		if (index < keyStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "i.") #>
<#
	}
#>
					AS KEY_VAL
				FROM 
					INSERTED i
			) RES
	END
	-- DELETE
	ELSE IF @COUNT_NEW = 0 AND @COUNT_OLD > 0
	BEGIN
		INSERT INTO <#= schemaName #>.DATA_AUDIT
		(
			LST_CHG_TMS,
			LST_CHG_USR_ID,
			TARGET_TABLE,
			ACTION,
			RECORD_KEY,
			OLD_ROW_DETAILS,
			NEW_ROW_DETAILS
		)
		SELECT
			@CURR_TIME,
			RTRIM(@USER_ID),
			'<#= tableName #>',
			'Delete',
			ISNULL(RES.KEY_VAL,'UNKNOWN'),
			ISNULL(RES.OLD_XML_OUT,''),
			''
			FROM
			(
				SELECT
<# 
	for (int index = 0; index < valueStrs.Count(); index++) {
		var line = valueStrs[index];
		if (index < valueStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "d.") #>
<#
	}
#>
					AS OLD_XML_OUT,
<# 
	for (int index = 0; index < keyStrs.Count(); index++) {
		var line = keyStrs[index];
		if (index < keyStrs.Count() - 1)
		{
			line += "|' + ";
		}
		else
		{
			line += "'";
		}
#>
					<#= String.Format(line, "d.") #>
<#
	}
#>
					AS KEY_VAL
				FROM 
					DELETED d
			) RES
	END
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

通过上面的template，我们只需要提供数据库Connection String和需要Audit的表的名字，Trigger就可以自动生成了