根据设置的表和字段动态生成触发器记录数据的变动日志

本SQL代码主要功能为根据业务人员设置的监控字段（保存在数据库AuditLogType表的监控规则）； 减去了数据表操作日志代码的编写；并且系统管理员可以根据数据库服务器性能自由调整监控字段的量。可以做到一次开发；多次使用。

以下代码是生成日志表(AuditLogging)和设置监控字段表(AuditLogType);

GetUserID 函数为获得当前操作数据库的用户；此函数可以和业务系统的用户对接；可以在找不到业务系统用户的情况下；获得数据库的SUSER_NAME(如sa等数据库用户)；这样不管是业务系统修改数据；还是从数据库直接修改数据都可以记录。

缺点是监控表设置太多，生成太多的触发器，将影响服务器的性能；日志表(AuditLogging)需要定期转存或者清除；提升日志表(AuditLogging)查询速度。

CREATE TABLE [dbo].[AuditLogging](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MessageID] [int] NULL,
    [CreateID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogging_CreateID]  DEFAULT ([dbo].[GetUserID]()),
    [RecordID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogging_RecordID]  DEFAULT ([dbo].[GetUserID]()),
    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogging_CreateDate]  DEFAULT (getdate()),
    [RecordDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogging_RecordDate]  DEFAULT (getdate()),
    [RowPointer] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AuditLogging_RowPointer]  DEFAULT (newid()),
    [Stat] [nvarchar](10) NULL,
    [TableName] [nvarchar](50) NOT NULL,
    [TableRowPointer] [uniqueidentifier] NULL,
    [FieldName] [nvarchar](50) NULL,
    [OldValue] [nvarchar](4000) NULL,
    [NewValue] [nvarchar](4000) NULL,
    [KeyValue] [nvarchar](4000) NULL,
 CONSTRAINT [PK_AuditLogging] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[AuditLogType](
    [MessageID] [int] IDENTITY(1,1) NOT NULL,
    [Stat] [nvarchar](50) NULL CONSTRAINT [DF_AuditLogType_Stat]  DEFAULT (''),
    [Enabled] [bit] NULL CONSTRAINT [DF_AuditLogType_Enabled]  DEFAULT ((0)),
    [TableName] [nvarchar](50) NOT NULL,
    [FieldName] [nvarchar](50) NOT NULL,
    [CreateID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogType_CreateID]  DEFAULT ([dbo].[GetUserID]()),
    [RecordID] [nvarchar](30) NOT NULL CONSTRAINT [DF_AuditLogType_UpID]  DEFAULT ([dbo].[GetUserID]()),
    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogType_CreateDate1]  DEFAULT (getdate()),
    [RecordDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLogType_UpDate]  DEFAULT (getdate()),
    [RowPointer] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AuditLogType_RowPointer]  DEFAULT (newid()),
 CONSTRAINT [PK_AuditLogType_1] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

以下是动态生成触发器的代码

USE [Design2012]
GO
/****** 对象:  StoredProcedure [dbo].[AuditGen]    脚本日期: 06/20/2012 21:39:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AuditGen] 
AS
begin
DECLARE @SQL NVARCHAR(4000)
SET @SQL=''
SELECT @SQL=@SQL+'exec AuditGenCode '''+TableName+'''   ' FROM AuditLogType
WHERE [Enabled] =0
GROUP BY TableName
declare @参数 nvarchar(500)
EXEC sp_executesql @SQL,N'@参数 varchar(20)', @参数 
END 

  
GO
/****** 对象:  StoredProcedure [dbo].[AuditGenCode]    脚本日期: 06/20/2012 21:39:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
CREATE PROC [dbo].[AuditGenCode]
( @TableName   sysname)
AS
BEGIN 
DECLARE @Temp TABLE (
   Stat CHAR(3)
   , CodeLine NVARCHAR(4000) NULL
   , LineNum  INT IDENTITY
   )
DECLARE @Stat NVARCHAR(30)
IF OBJECT_ID(@TableName) IS NULL
BEGIN
    SELECT
     CodeLine
    FROM @Temp
    ORDER BY LineNum

    --RETURN 0
END

   SET @Stat='010'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF (OBJECT_ID(N''[dbo].[@TableNameInsAudit]'') IS NOT NULL )')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'  DROP TRIGGER [dbo].[@TableNameInsAudit] ')

   SET @Stat='020'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF (OBJECT_ID(N''[dbo].[@TableNameUdpAudit]'') IS NOT NULL )')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'  DROP TRIGGER [dbo].[@TableNameUdpAudit] ')

   SET @Stat='030'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF (OBJECT_ID(N''[dbo].[@TableNameDelAudit]'') IS NOT NULL )')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'  DROP TRIGGER [dbo].[@TableNameDelAudit] ')

   SET @Stat='Gen'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'INSERT INTO dbo.AuditLogging(')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       MessageID ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       CreateID ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       RecordID ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       CreateDate ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       RecordDate ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       RowPointer ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       Stat ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       TableName ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       TableRowPointer ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       FieldName ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       OldValue ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       NewValue ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       KeyValue')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       )')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SELECT ')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @MessageID as MessageID,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @CreateID as CreateID,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @RecordID as RecordID,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       GETDATE() as CreateDate ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       GETDATE() as RecordDate ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       newid() as RowPointer ,')
   SET @Stat='GeD'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       ''Del'' as Stat ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @TableNam as TableName ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       deleted.RowPointer AS TableRowPointer ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @FieldName as FieldName,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @OldValue AS OldValue , ')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       NULL AS NewValue ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @KeyValue AS KeyValue')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       FROM deleted')
   SET @Stat='GeI'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       ''Ins'' as Stat ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @TableNam as TableName ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       INSERTED.RowPointer AS TableRowPointer ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @FieldName as FieldName,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       NULL AS OldValue , ')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @NewValue AS NewValue ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @KeyValue AS KeyValue')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       FROM INSERTED')
   SET @Stat='GeU'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       ''Upd'' as Stat ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @TableNam as TableName ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       INSERTED.RowPointer AS TableRowPointer ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @FieldName as FieldName,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @OldValue AS OldValue , ')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @NewValue AS NewValue ,')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       @KeyValue AS KeyValue')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       FROM INSERTED')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       INNER JOIN deleted ON inserted.RowPointer = deleted.RowPointer')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       WHERE ISNULL(deleted.@FieldNam, NCHAR(1))')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       <> ISNULL(inserted.@FieldNam, NCHAR(1))')
   SET @Stat='Ge1'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Severity = @@Error')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF @Severity <> 0') 
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'BEGIN')  
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       SET @Infobar = ''@TableName.@FieldName''')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       GOTO EOT') 
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'END') 
   SET @Stat='Ge2'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'EOT:')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF @Severity <> 0')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'BEGIN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       SET @Infobar = ''Internal error: Unable to Create Audit Log for Del of '' + @Infobar')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       raiserror (@Infobar, @Severity, 3)')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       if @Severity > 5 or @Severity < 0') 
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       BEGIN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'              ROLLBACK TRANSACTION')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'              RETURN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'       END')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'END')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'')
   --INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'RETURN')
   SET @Stat='001'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'Create TRIGGER @TableNameInsAudit')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'ON [dbo].[@TableName]')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AFTER INSERT')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AS')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF @@ROWCOUNT = 0 RETURN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE  @Severity INT, @Infobar  nvarchar(4000)')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Severity = 0')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Infobar = NULL')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE @CreateID nvarchar(50),@RecordID nvarchar(50)')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @CreateID=@UserName')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @RecordID=@UserName')
   SET @Stat='002'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'Create TRIGGER [dbo].[@TableNameUdpAudit]')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'ON [dbo].[@TableName]')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AFTER UpDate')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AS')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF @@ROWCOUNT = 0 RETURN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE  @Severity INT, @Infobar  nvarchar(4000)')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Severity = 0')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Infobar = NULL')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE @CreateID nvarchar(50),@RecordID nvarchar(50)')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @CreateID=@UserName')
--   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @RecordID=@UserName')
   SET @Stat='003'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'Create TRIGGER [dbo].[@TableNameDelAudit]')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'ON [dbo].[@TableName]')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AFTER Delete')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'AS')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'IF @@ROWCOUNT = 0 RETURN')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE  @Severity INT, @Infobar  nvarchar(4000)')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Severity = 0')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @Infobar = NULL')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'DECLARE @CreateID nvarchar(50),@RecordID nvarchar(50)')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @CreateID=@UserName')
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,'SET @RecordID=@UserName')
   SET @Stat='A03'
   INSERT INTO @Temp(Stat,CodeLine)VALUES( @Stat,' UPDATE AuditLogType SET [Enabled]=1 WHERE [Enabled]=0 and TableName = @TableNam')
   
 
 
DECLARE @StandIn sysname
SET @StandIn = N'<tablename>'

DECLARE @pkc TABLE (column_name sysname, ordinal_position SMALLINT, data_type sysname)
INSERT INTO @pkc
SELECT pkc.column_name, pkc.ordinal_position, syst.name
FROM dbo.PrimaryKeyColumns(@TableName) AS pkc
INNER JOIN syscolumns AS sysc ON sysc.name = pkc.column_name
INNER JOIN systypes AS syst ON syst.xusertype = sysc.xtype
WHERE sysc.id = OBJECT_ID(@TableName)

DECLARE @KeyValue NVARCHAR(4000)
SET @KeyValue = N''

SELECT @KeyValue = @KeyValue
   + CASE WHEN @KeyValue = N'' THEN N'' ELSE N' + N''-'' + ' END
   + CASE WHEN pkc.data_type NOT IN ('nvarchar', N'varchar', N'char', N'nchar', N'ntext', N'text')
      THEN N'CAST(' ELSE N'' END
   + @StandIn + '.' + pkc.column_name
   + CASE WHEN pkc.data_type NOT IN ('nvarchar', N'varchar', N'char', N'nchar', N'ntext', N'text')
      THEN N' AS NVARCHAR(4000))' ELSE N'' END
FROM @pkc AS pkc
ORDER BY pkc.ordinal_position
 
 
DECLARE
  @ColumnId       INT
, @ColumnName     sysname
, @CASTit bit
, @Severity       INT
, @ALTMessageType int
, @SafeColumnName NVARCHAR(4000)

SET @Severity = 0
 
DECLARE AuditLogTypesCrs CURSOR LOCAL STATIC
FOR 
SELECT DISTINCT sysc.colid, sysc.name
   , CASE WHEN syst.name NOT IN ('nvarchar', N'varchar', N'char', N'nchar', N'ntext', N'text') THEN 1 ELSE 0 END
FROM syscolumns AS sysc
INNER JOIN AuditLogType AS ALT
   ON (ALT.FieldName = sysc.name OR ALT.FieldName = N'*')
INNER JOIN systypes AS syst ON syst.xusertype = sysc.xtype
WHERE sysc.id = OBJECT_ID(@TableName)
AND sysc.name NOT IN (N'CreateID', N'RecordID', N'CreateDate', N'RecordDate')
AND syst.name NOT IN (N'text', N'ntext', N'image')
AND sysc.iscomputed = 0
AND ALT.TableName = @TableName AND (ALT.Stat<>'Del' OR ALT.Stat IS NULL)
ORDER BY sysc.colid
OPEN AuditLogTypesCrs
WHILE @Severity = 0
BEGIN 
   FETCH AuditLogTypesCrs INTO
     @ColumnId
   , @ColumnName
   , @CASTit

   IF @@FETCH_STATUS = -1
      BREAK

   SET @ALTMessageType = N''
   SELECT TOP 1
      @ALTMessageType = ALT.MessageID
   FROM AuditLogType AS ALT
   WHERE          TableName = @TableName  AND
         (FieldName   = @ColumnName OR FieldName = N'*')

   SET @SafeColumnName =
      CASE @CASTit WHEN 1 THEN N'CAST(' ELSE N'' END
      + @StandIn + '.' + @ColumnName
      + CASE @CASTit WHEN 1 THEN N' AS NVARCHAR(4000))' ELSE N'' END
      
 
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '001',CodeLine FROM @Temp WHERE Stat='Gen'
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '001',REPLACE(CodeLine,'@FieldName',''''+@ColumnName+'''') FROM @Temp WHERE Stat='GeI'   
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '001',REPLACE(CodeLine,'@FieldName',@ColumnName) FROM @Temp WHERE Stat='Ge1'   
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@MessageID',''''+CAST(@ALTMessageType AS NVARCHAR(50))+'''') WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@NewValue','Inserted.'+@ColumnName) WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@OldValue','Deleted.'+@ColumnName) WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@KeyValue',REPLACE(@KeyValue,'<tablename>','Inserted')) WHERE Stat='001'



   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002','if update(@FieldNam)'
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002','Begin'
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002',CodeLine FROM @Temp WHERE Stat='Gen'
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002',REPLACE(CodeLine,'@FieldName',''''+@ColumnName+'''') FROM @Temp WHERE Stat='GeU' 
  UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@FieldNam',@ColumnName) FROM @Temp WHERE Stat='002'     
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002',REPLACE(CodeLine,'@FieldName',@ColumnName) FROM @Temp WHERE Stat='Ge1'    
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002','End'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@MessageID',''''+CAST(@ALTMessageType AS NVARCHAR(50))+'''') WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@NewValue','Inserted.'+@ColumnName) WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@OldValue','Deleted.'+@ColumnName) WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@KeyValue',REPLACE(@KeyValue,'<tablename>','Inserted')) WHERE Stat='002'

   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '003',CodeLine FROM @Temp WHERE Stat='Gen'
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '003',REPLACE(CodeLine,'@FieldName',''''+@ColumnName+'''') FROM @Temp WHERE Stat='GeD'      
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '003',REPLACE(CodeLine,'@FieldName',@ColumnName) FROM @Temp WHERE Stat='Ge1'  
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@MessageID',''''+CAST(@ALTMessageType AS NVARCHAR(50))+'''') WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@NewValue','Inserted.'+@ColumnName) WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@OldValue','Deleted.'+@ColumnName) WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@KeyValue',REPLACE(@KeyValue,'<tablename>','Deleted')) WHERE Stat='003'

END 

CLOSE AuditLogTypesCrs
DEALLOCATE AuditLogTypesCrs
   
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '001',CodeLine FROM @Temp WHERE Stat='GeA'

   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002',CodeLine FROM @Temp WHERE Stat='GeB'
   
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '003',CodeLine FROM @Temp WHERE Stat='GeC'
   
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '001',CodeLine FROM @Temp WHERE Stat='Ge2'

   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '002',CodeLine FROM @Temp WHERE Stat='Ge2'
   
   INSERT INTO @Temp(Stat,CodeLine)
   SELECT '003',CodeLine FROM @Temp WHERE Stat='Ge2'
   
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@CreateID','inserted.RecordID') WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@RecordID','inserted.RecordID') WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@CreateID','inserted.RecordID') WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@RecordID','inserted.RecordID') WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@UserName','dbo.GetUserID()') WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableNam',''''+@TableName+'''') WHERE Stat='001'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableNam',''''+@TableName+'''') WHERE Stat='002'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableNam',''''+@TableName+'''') WHERE Stat='003'
   UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@MessageID',''''+CAST(@ALTMessageType AS NVARCHAR(50))+'''') WHERE Stat='A03'
 
    UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='010'
    UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='020'
    UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableName',''+@TableName) WHERE Stat='030'
    UPDATE @Temp SET CodeLine=REPLACE(CodeLine,'@TableNam',''''+@TableName+'''') WHERE Stat='A03'
    declare @参数 nvarchar(20),@SQL nvarchar(4000) 
    
    SET @SQL=''
    SELECT @SQL=@SQL+CodeLine FROM @Temp WHERE Stat='010'
    EXEC sp_executesql @SQL,N'@参数 varchar(20)', @参数 
    SET @SQL=''
    SELECT @SQL=@SQL+CodeLine FROM @Temp WHERE Stat='020'
    EXEC sp_executesql @SQL,N'@参数 varchar(20)', @参数 
    SET @SQL=''
    SELECT @SQL=@SQL+CodeLine FROM @Temp WHERE Stat='030'
    EXEC sp_executesql @SQL,N'@参数 varchar(20)', @参数 
    DELETE FROM dbo.AuditLogType WHERE TableName = @TableName AND Stat='Del'
    IF EXISTS(SELECT TableName FROM dbo.AuditLogType ALT WHERE ALT.TableName = @TableName AND (ALT.Stat<>'Del' OR ALT.Stat IS NULL))
    begin
    SELECT CodeLine FROM @Temp WHERE Stat='010'
    SELECT CodeLine FROM @Temp WHERE Stat='020'
    SELECT CodeLine FROM @Temp WHERE Stat='030'
    SELECT CodeLine AS CodeLine FROM @Temp WHERE Stat='001'
    UNION ALL
    SELECT '@Error'
    UNION ALL
    SELECT CodeLine AS CodeLine FROM @Temp WHERE Stat='002'
    UNION ALL
    SELECT '@Error'
    UNION ALL
    SELECT CodeLine AS CodeLine FROM @Temp WHERE Stat='003'
    UNION ALL
    SELECT '@Error'
    UNION ALL
    SELECT CodeLine AS CodeLine FROM @Temp WHERE Stat='A03'
    UNION ALL
    SELECT '@Error'
    end
END 

--exec AuditGen 
--EXEC [AuditGenCode] 'exec AuditGen '