SQL Server TRIGGER that will INSERT or UPDATE another table

Question

I have a SQL Server table called Prices, which contains tens of thousands of rows of data. This table is used heavily by legacy applications and unfortunately cannot be modified (no columns can be added, removed, or modified).

My requirement is to keep track of when the table is modified (INSERT, UPDATE, or DELETE). However, the Prices table does not have a LastUpdated column, and I am not able to add such column. Additionally, my trigger must be compatible with SQL Server 2005.

I can however create an additional table, PricesHistory which will store the PriceID, UpdateType and LastUpdated columns.

I want to attach a SQL TRIGGER to the Prices table that will either INSERT or UPDATE a row in the PricesHistory table which will keep track of when the prices were last updated and what operation triggered it.

Here is what I have so far, which will detect which operation caused the trigger to fire. However, I'm stumped on how to SELECT from either inserted or deleted tables and do a proper INSERT/UPDATE to the PricesHistory table.

Basically, all operations should check if the PriceID already exists in the PriceHistory table, and UPDATE the UpdateType and LastUpdated columns. If the PriceID does not exist yet, it should INSERT it along with the UpdateType and LastUpdated values.

EDIT: It has been brought to my attention by a co-worker that the inserted and deleted items are rows not tables. Meaning that I could do a simple IF EXISTS ... UPDATE ELSE INSERT INTO clause. Is this true? I was under the impression it would be a table of the rows, not individual rows.

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

Answer 1

Why not a generic audit table? See my presentation "How to prevent and audit changes?"

http://craftydba.com/?page_id=880

Here is a table to save the data being changed.

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

Here is a trigger to capture INS, UPD, DEL statements.

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

If you are having a-lot of changes to the table, then either purge data on a cycle or just record the modified date in another table like you stated. However, key information will be lost.

Nice thing about my solution is that it tells you when and who did the change. The actual data is save in XML format that can be restored if need be.