数据库不表只触发[关闭](Trigger on database not table only [c

当我们在一个表中插入或更新数据，在时间我想主键和表名到另一个表中插入。
我想这能在一天结束调和，今天更新的所有表。

请建议。谢谢。

Answer 1:

看看CDC - 变更数据捕获

或保罗尼尔森的AutoAudit 。

Answer 2:

SELECT name
FROM sys.objects
WHERE DATEDIFF(D,modify_date, GETDATE()) < 1

通过使用这种查询可以能得到您的数据库的所有修改。在地方的1你可以能够给任何你想看到的变化天。

Answer 3:

我已经制定了这样的事......我希望这回答了你的问题。

创建示范了几个测试表
创建SQL创建的所有表上的触发器
简单的插入语句。

  CREATE TABLE [dbo].[log_table]( [table_name] [varchar](1000) NULL, [column_name] [varchar](1000) NULL, [column_value] [varchar](1000) NULL, [datetime] [datetime] NULL ) GO

    CREATE TABLE [dbo].[Animals](
        [AnimalID] [int] IDENTITY(1,1) NOT NULL,
        [AnimalName] [varchar](50) NULL,
     CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC)
    )

    GO

    CREATE TABLE [dbo].[Countries](
        [CountryID] [uniqueidentifier] NOT NULL,
        [CountryName] [varchar](50) NULL,
     CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 
    ([CountryID] ASC))
    ALTER TABLE [dbo].[Countries] ADD  CONSTRAINT [DF_Countries_countryID]  DEFAULT (newid()) FOR [CountryID]
    GO

  SELECT ' IF OBJECT_ID ('+ CHAR(39) + SCHEMA_NAME(tbl.SCHEMA_ID) + '.trg_' + tbl.name + CHAR(39) + ',''TR'') IS NOT NULL ' + ' DROP TRIGGER ' + SCHEMA_NAME(tbl.SCHEMA_ID) + '.trg_'+ tbl.name + ';' + ' EXEC (' + CHAR(39) + ' CREATE TRIGGER ' + SCHEMA_NAME(tbl.SCHEMA_ID) + '.trg_'+ tbl.name + ' ON '+ SCHEMA_NAME(tbl.SCHEMA_ID) + '.' + tbl.name + ' FOR INSERT,UPDATE AS ' + ' DECLARE @newval varchar(1000) ' + ' SELECT @newval ='+ COL_NAME(tbl.object_id, ic.column_id) + ' FROM INSERTED' + ' INSERT INTO log_table(table_name,column_name,column_value,datetime) VALUES('+ CHAR(39)+ CHAR(39) + SCHEMA_NAME(tbl.SCHEMA_ID) +'.'+ tbl.name + CHAR(39)+ CHAR(39) + ' ,'+ CHAR(39)+ CHAR(39) + COL_NAME(tbl.object_id, ic.column_id) + CHAR(39)+ CHAR(39) + ' ,'+ '@newval,getdate()'+ ')' + CHAR(39)+ ')' FROM sys.tables AS tbl INNER JOIN sys.key_constraints AS kc ON kc.parent_object_id = tbl.object_id INNER JOIN sys.indexes AS i ON kc.unique_index_id = i.index_id AND kc.parent_object_id = i.object_id INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE tbl.type = 'U' AND ic.is_included_column = 0 and tbl.name NOT IN ('sysdiagrams','log_table') ORDER BY tbl.object_id, ic.key_ordinal;

现在，插入到我们的表

 INSERT INTO Countries(CountryName) VALUES('South Africa') INSERT INTO Countries(CountryName) VALUES('USA') INSERT INTO Countries(CountryName) VALUES('Brazil') INSERT INTO Countries(CountryName) VALUES('Zimbabwe') INSERT INTO Countries(CountryName) VALUES('Africa is not a country') INSERT INTO Animals(AnimalName) VALUES ('Lion') INSERT INTO Animals(AnimalName) VALUES ('African Elephant') INSERT INTO Animals(AnimalName) VALUES ('Rhinoceros') INSERT INTO Animals(AnimalName) VALUES ('Leopard') INSERT INTO Animals(AnimalName) VALUES ('Cape Buffalo')