SQL Server “复制”表结构，创建_Log表及触发器

实例效果：

实现表数据的增修删时，记录日志。

1.“复制”现有表，

创建相应的_Log表；

（注意点：

通过select union all 的方式，避免了IDENTITY 的“复制”，
即如果原表有 PK 如 ID Identity，_Log表 仅“复制”ID int，“不复制” Identity属性，
以便 Insert Update Delete时，可以Insert到Log表。）

2.对现有表，创建Insert，Update,Delete的触发器，

并将相应数据 记录到对应的_Log表

相应代码如下：

BEGIN TRAN

BEGIN TRY

--定义TAB_CURSOR

DECLARE TAB_CURSOR CURSOR read_only

FOR

   SELECT name FROM SysObjects Where XType='U'

  -- AND name = N'T01ConstItem'

  and [name] <> N'dtproperties'

   ORDER BY Name;

--打开

OPEN TAB_CURSOR

DECLARE @P_TabName NVARCHAR(200);

DECLARE @P_TabName_Log NVARCHAR(200);

DECLARE @P_Create_Log_Tab NVARCHAR(4000);

DECLARE @P_Create_Trig_I NVARCHAR(4000);

DECLARE @P_Create_Trig_U NVARCHAR(4000);

DECLARE @P_Create_Trig_D NVARCHAR(4000);

FETCH NEXT FROM TAB_CURSOR

           INTO @P_TabName

--循环

WHILE (@@FETCH_STATUS <> -1)

BEGIN

   IF (@@FETCH_STATUS <> -2)

    BEGIN

    SET @P_TabName_Log = CONCAT(@P_TabName,N'_Log');

    SET @P_Create_Log_Tab = N' SELECT * ';

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' INTO ');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName_Log );

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' UNION ALL ');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' SELECT TOP (1) * ');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);

    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab, N' WHERE 1=0 ; ');

    EXEC( @P_Create_Log_Tab);

    --SET @P_Create_Log_Tab = CONCAT(N' SET IDENTITY_INSERT ',@P_TabName_Log ,' ON ');

    --EXEC( @P_Create_Log_Tab);

    SET @P_Create_Trig_I = N' create trigger ';

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' trig_',@P_TabName,N'_I ');

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' on ',@P_TabName,N' after INSERT as ');

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N' begin ');

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'insert into ',@P_TabName_Log );

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' select * , N''I'',Getdate() from Inserted ; ' );

    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'end ');

    --select @P_Create_Trig_I;

    EXEC( @P_Create_Trig_I);

    SET @P_Create_Trig_U = N' create trigger ';

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_U ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after UPDATE as ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UD'',Getdate() from Deleted ; ' );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UI'',Getdate() from Inserted ; ' );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');

    EXEC( @P_Create_Trig_U);

    SET @P_Create_Trig_U = N' create trigger ';

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_D ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after DELETE as ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''D'',Getdate() from Deleted ; ' );

    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');

    EXEC( @P_Create_Trig_U);

    END

   FETCH NEXT FROM TAB_CURSOR INTO @P_TabName

END

--关闭

CLOSE TAB_CURSOR

--释放

DEALLOCATE TAB_CURSOR

COMMIT TRAN;

END TRY

BEGIN CATCH

    SELECT ERROR_MESSAGE() AS ErrorMessage

    ,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState

    ROLLBACK TRAN;

END CATCH