检测mysql表更新吗_sql server 数据库检测表更新

-- 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