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

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

Solution 2

the code i am giving from there you can get the view how to write single trigger for handling database insert & update and as well as how to do audit trail by trigger. hope this help.

CREATE TRIGGER TRG_HourSheet ON EditedHourSheet 
FOR INSERT, UPDATE
AS                     
   DECLARE @v_xml   XML,
   @PKValue INT,                    
   @type   CHAR(1),
   @v_slno   INT               

BEGIN                    
 SET NOCOUNT ON                    


IF EXISTS(SELECT * FROM INSERTED)
BEGIN
  IF EXISTS(SELECT * FROM DELETED)
  BEGIN
     SET @type ='U';
  END
  ELSE
  BEGIN
    SET @type ='I';
  END
END

IF @type = 'U'
BEGIN   
    DECLARE DB_CURSOR CURSOR FOR                     
    SELECT ID FROM DELETED ORDER BY ModDate DESC                     

    OPEN DB_CURSOR                      
    FETCH NEXT FROM DB_CURSOR INTO @PKValue                      
    WHILE @@FETCH_STATUS = 0                       
    BEGIN                    
    SET  @v_xml =(SELECT * FROM DELETED Where ID=@PKValue          
       FOR xml AUTO, root('Record'),elements XSINIL)                    

    SELECT @v_slno = IsNull(Max(RowID),0)+1 FROM EditedHourSheetLog  
    Where HourSheetID=@PKValue           

    INSERT INTO EditedHourSheetLog(HourSheetID,XMLData,Action,RowID)                    
    values (@PKValue,@v_xml,@type,@v_slno)                    
    FETCH NEXT FROM DB_CURSOR INTO @PKValue                      

    END                    

    CLOSE DB_CURSOR                      
    DEALLOCATE DB_CURSOR                    
    --END 
END 
ELSE IF @type = 'I'
BEGIN   
    DECLARE DB_CURSOR CURSOR FOR                     
    SELECT ID FROM INSERTED ORDER BY ModDate DESC                     

    OPEN DB_CURSOR                      
    FETCH NEXT FROM DB_CURSOR INTO @PKValue                      
    WHILE @@FETCH_STATUS = 0                       
    BEGIN                    
    SET  @v_xml =(SELECT * FROM INSERTED Where ID=@PKValue          
       FOR xml AUTO, root('Record'),elements XSINIL)                    

    SELECT @v_slno = IsNull(Max(RowID),0)+1 FROM EditedHourSheetLog  
    Where HourSheetID=@PKValue           

    INSERT INTO EditedHourSheetLog(HourSheetID,XMLData,Action,RowID)                    
    values (@PKValue,@v_xml,@type,@v_slno)                    
    FETCH NEXT FROM DB_CURSOR INTO @PKValue                      

    END                    

    CLOSE DB_CURSOR                      
    DEALLOCATE DB_CURSOR                    
    --END 
END
END

Comments

[Erik]

This is really neat, though may a bit over my head at first glance. I'll take a look at it and see if I can do similar. I like the idea for sure!

[CRAFTY DBA]

Download my presentation bundle. Create the AUTOS database and play around. It is not that hard.

[BateTech]

I like this idea! I think I will modify and use for our delete audit templates, but I prefer old/new values for updates, and PK only for inserts. One thing to be careful of is if you have a massive insert, update, or delete then you could possibly hit the 2GB max limit for the XML data type which could kill your transaction (although it would have to be a LOT of data being updated).

[CRAFTY DBA]

Yes, but I usually delete large data in small batches. craftydba.com/?p=3079 It is ideal for tables with small changes. We use it for key recipe data for audit trails.

[dburges]

Never under any circumstances use a cursor in a trigger.

[Thomas]

if lots of rows update manually by using update statement then trigger may not be able to capture all the update so that is why i use cursor. i am clear.

[dburges]

Cursors are slower than set based things and if a lot of records were updated then you will have a performance issue that willock up the table possibly for hours. I removed a cursor from one of our triggers and improved the insert of 40,000 records from 40 minutes to seconds. Triggers must run as fast as possible. Use an insert using a select rather than a values clause instead.

[Thomas]

my question if anyone update multiple row then how your trigger can capture those updated rows if cursor is not used inside trigger. can u show me any example.

[dburges]

you join to the inserted and/or deleted tables. You never set anythign to a scalar variable and ifyou are inserting to another table you use a select instead of a values clause for the insert.