执行以下T-SQL语句，将在当前数据库中创建一个日志表 [dbo].[generic_tbmodify_log] ，并自动从 sysobjects 系统表中获得数据库表清单，对除 [dbo].[generic_tbmodify_log] 本身以外的所有用户表创建触发器（触发器名形如 TG_FOR_GENETBMODIFY_[表名] ）。这样当这些表发生 insert / update / delete 的时候，会自动更新 generic_tbmodify_log 表相应表名的表数据最后更新时间。

-- create table to hold updated message and date -- tested in MS SQL Server 2000 if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[generic_tbmodify_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 ) drop table [dbo].[generic_tbmodify_log] GO CREATE TABLE [dbo].[generic_tbmodify_log] ([PKID] [int] IDENTITY (1, 1) NOT NULL , [tb_name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [info] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [last_modified] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[generic_tbmodify_log] WITH NOCHECK ADD CONSTRAINT [PK_generic_tbmodify_log] PRIMARY KEY CLUSTERED ([PKID]) ON [PRIMARY] GO -- for each table in database generate audit trigger -- except generic_tbmodify_log, dtproperties DECLARE @TABLENAME VARCHAR(50) DECLARE @SQLCMD VARCHAR(2000) DECLARE TABLES_CURSOR CURSOR FOR SELECT Name from dbo.sysobjects where xtype = 'U' AND NAME <> 'dtproperties' AND NAME <> 'generic_tbmodify_log' OPEN TABLES_CURSOR FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME WHILE (@@FETCH_STATUS = 0) BEGIN -- BUILD CREATE TRIGGER STATEMENT SET @SQLCMD = (SELECT 'CREATE TRIGGER [TG_FOR_GENETBMODIFY_') SET @SQLCMD = @SQLCMD + @TABLENAME + '] ON [DBO].[' + @TABLENAME + ']' + ' FOR INSERT, UPDATE, DELETE AS' + ' DECLARE @CNTINSERT INTEGER' + ' DECLARE @CNTDELETE INTEGER' + ' DECLARE @INFOSTR VARCHAR(128)' + ' SELECT @CNTINSERT = (SELECT COUNT(*) FROM Inserted)' + ' SELECT @CNTDELETE = (SELECT COUNT(*) FROM Deleted)' + ' SET @INFOSTR=''''' + ' IF @CNTINSERT > 0 BEGIN' + ' IF @CNTDELETE > 0' + ' SET @INFOSTR = @INFOSTR + ''UPD:''' + ' ELSE' + ' SET @INFOSTR = @INFOSTR + ''INS:''' + ' SET @INFOSTR = @INFOSTR + '+ ' master.dbo.fn_varbintohexsubstring('+ ' 1,COLUMNS_UPDATED(),1,0)' + ' END ELSE SET @INFOSTR = @INFOSTR + ''DEL''' + ' IF NOT EXISTS' + ' (SELECT DBA.TB_NAME FROM' + ' DBO.GENERIC_TBMODIFY_LOG DBA' + ' WHERE TB_NAME = ''' + @TABLENAME + ''')' + ' BEGIN' + ' INSERT INTO GENERIC_TBMODIFY_LOG' + ' (tb_name,info,last_modified) VALUES ' + ' (''' + @TABLENAME + ''',' + ' @INFOSTR,' + ' Getdate()' + ' )' + ' END' + ' ELSE' + ' BEGIN' + ' UPDATE GENERIC_TBMODIFY_LOG SET' + ' info=@INFOSTR,' + ' last_modified=GETDATE()' + ' WHERE tb_name=''' + @TABLENAME + '''' + ' END' -- EXECUTE CREATE TRIGGER STATEMENT EXEC (@SQLCMD) FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME END CLOSE TABLES_CURSOR DEALLOCATE TABLES_CURSOR

相应的如果要移除以上脚本创建的所有trigger，可以执行以下语句：

declare @name varchar(500) declare mycursor cursor for select name from sysobjects where type = 'tr' and name like 'TG_FOR_GENETBMODIFY_%' open mycursor fetch next from mycursor into @name while @@fetch_status = 0 begin exec ('drop trigger ' + @name) select 'Deleted ' + @name fetch next from mycursor into @name end close mycursor deallocate mycursor