我有一个名为SQL Server的表Prices
,其中包含数万行数据.遗留应用程序大量使用此表,遗憾的是无法修改此表(不能添加,删除或修改列).
我的要求是跟踪表的修改时间(INSERT, UPDATE, or DELETE
).但是,该Prices
表没有LastUpdated
列,我无法添加此列.此外,我的触发器必须与SQL Server 2005兼容.
不过,我可以创建一个额外的表,PricesHistory
将存储PriceID
,UpdateType
和LastUpdated
列.
我想SQL TRIGGER
在Prices
表格中附加一个表格,该表格将在表格中的一行INSERT
或UPDATE
一行中PricesHistory
跟踪价格上次更新的时间以及触发它的操作.
这是我到目前为止所做的,它将检测哪个操作导致触发器触发.但是,我很难知道如何SELECT
从表格inserted
或deleted
表格中做出适当INSERT/UPDATE
的PricesHistory
表格.
基本上,所有的操作都应该检查是否PriceID
已经在存在PriceHistory
表,并UPDATE
在UpdateType
与LastUpdated
列.如果PriceID
它还不存在,它应该INSERT
和the UpdateType
和LastUpdated
值一起使用.
编辑:这是由一位同事的提醒我注意inserted
和deleted
项目行不表.这意味着我可以做一个简单的IF EXISTS ... UPDATE ELSE INSERT INTO
条款.这是真的?我的印象是它将是行的表,而不是单独的行.
CREATE TRIGGER PricesUpdateTrigger ON Prices AFTER INSERT, UPDATE, DELETE AS DECLARE @UpdateType nvarchar(1) DECLARE @UpdatedDT datetime SELECT @UpdatedDT = CURRENT_TIMESTAMP IF EXISTS (SELECT * FROM inserted) IF EXISTS (SELECT * FROM deleted) SELECT @UpdateType = 'U' -- Update Trigger ELSE SELECT @UpdateType = 'I' -- Insert Trigger ELSE IF EXISTS (SELECT * FROM deleted) SELECT @UpdateType = 'D' -- Delete Trigger ELSE SELECT @UpdateType = NULL; -- Unknown Operation IF @UpdateType = 'I' BEGIN -- Log an insertion record END IF @UpdateType = 'U' BEGIN -- Log an update record END IF @UpdateType = 'D' BEGIN -- Log a deletion record END GO
CRAFTY DBA.. 6
为什么不是通用审计表?请参阅我的演讲"如何预防和审核变更?"
http://craftydba.com/?page_id=880
这是一个用于保存要更改的数据的表.
-- -- 7 - Auditing data changes (table for DML trigger) -- -- Delete existing table IF OBJECT_ID('[AUDIT].[LOG_TABLE_CHANGES]') IS NOT NULL DROP TABLE [AUDIT].[LOG_TABLE_CHANGES] GO -- Add the table CREATE TABLE [AUDIT].[LOG_TABLE_CHANGES] ( [CHG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [CHG_DATE] [datetime] NOT NULL, [CHG_TYPE] [varchar](20) NOT NULL, [CHG_BY] [nvarchar](256) NOT NULL, [APP_NAME] [nvarchar](128) NOT NULL, [HOST_NAME] [nvarchar](128) NOT NULL, [SCHEMA_NAME] [sysname] NOT NULL, [OBJECT_NAME] [sysname] NOT NULL, [XML_RECSET] [xml] NULL, CONSTRAINT [PK_LTC_CHG_ID] PRIMARY KEY CLUSTERED ([CHG_ID] ASC) ) ON [PRIMARY] GO -- Add defaults for key information ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [CHG_DATE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [CHG_TYPE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [CHG_BY]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [APP_NAME]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [HOST_NAME]; GO
这是捕获INS,UPD,DEL语句的触发器.
-- -- 8 - Make DML trigger to capture changes -- -- Delete existing trigger IF OBJECT_ID('[ACTIVE].[TRG_FLUID_DATA]') IS NOT NULL DROP TRIGGER [ACTIVE].[TRG_FLUID_DATA] GO -- Add trigger to log all changes CREATE TRIGGER [ACTIVE].[TRG_FLUID_DATA] ON [ACTIVE].[CARS_BY_COUNTRY] FOR INSERT, UPDATE, DELETE AS BEGIN -- Detect inserts IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'INSERT', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END -- Detect deletes IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'DELETE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END -- Update inserts IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'UPDATE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END END; GO
如果您对表进行了大量更改,则可以在一个循环中清除数据,或者只是将修改后的日期记录在另一个表中,如您所述.但是,关键信息将丢失.
关于我的解决方案的好处是它告诉你何时以及谁做了改变.实际数据以XML格式保存,如果需要可以恢复.
为什么不是通用审计表?请参阅我的演讲"如何预防和审核变更?"
http://craftydba.com/?page_id=880
这是一个用于保存要更改的数据的表.
-- -- 7 - Auditing data changes (table for DML trigger) -- -- Delete existing table IF OBJECT_ID('[AUDIT].[LOG_TABLE_CHANGES]') IS NOT NULL DROP TABLE [AUDIT].[LOG_TABLE_CHANGES] GO -- Add the table CREATE TABLE [AUDIT].[LOG_TABLE_CHANGES] ( [CHG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [CHG_DATE] [datetime] NOT NULL, [CHG_TYPE] [varchar](20) NOT NULL, [CHG_BY] [nvarchar](256) NOT NULL, [APP_NAME] [nvarchar](128) NOT NULL, [HOST_NAME] [nvarchar](128) NOT NULL, [SCHEMA_NAME] [sysname] NOT NULL, [OBJECT_NAME] [sysname] NOT NULL, [XML_RECSET] [xml] NULL, CONSTRAINT [PK_LTC_CHG_ID] PRIMARY KEY CLUSTERED ([CHG_ID] ASC) ) ON [PRIMARY] GO -- Add defaults for key information ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [CHG_DATE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [CHG_TYPE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [CHG_BY]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [APP_NAME]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [HOST_NAME]; GO
这是捕获INS,UPD,DEL语句的触发器.
-- -- 8 - Make DML trigger to capture changes -- -- Delete existing trigger IF OBJECT_ID('[ACTIVE].[TRG_FLUID_DATA]') IS NOT NULL DROP TRIGGER [ACTIVE].[TRG_FLUID_DATA] GO -- Add trigger to log all changes CREATE TRIGGER [ACTIVE].[TRG_FLUID_DATA] ON [ACTIVE].[CARS_BY_COUNTRY] FOR INSERT, UPDATE, DELETE AS BEGIN -- Detect inserts IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'INSERT', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END -- Detect deletes IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'DELETE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END -- Update inserts IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'UPDATE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type) RETURN; END END; GO
如果您对表进行了大量更改,则可以在一个循环中清除数据,或者只是将修改后的日期记录在另一个表中,如您所述.但是,关键信息将丢失.
关于我的解决方案的好处是它告诉你何时以及谁做了改变.实际数据以XML格式保存,如果需要可以恢复.