SQL Server TRIGGER that will INSERT or UPDATE another table

database, sql-server, sql-server-2005, t-sql, triggers

Solution

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.

Problem

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

Original source