CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
AaA
AaA

Reputation: 3694

Insert records to another table with trigger if row changes except one column

Case

I have two tables with following structures (I removed 34 other columns that were not related to question)

Table1
------
Id
LastAccessed
Data1
Data2

Table1_History
------
_Id
Action
Id
LastAccessed
Data1
Data2

Every time a user reads the record (with specific procedure) the LastAccessed timestamp will change.

I attached a trigger to Table1 which copies the record to history table if Data1 or Data2 (or in my case any other column changes); However I don't want a copy or record in case LastAccessed changes.

Here is my trigger

CREATE TRIGGER [TRIGGER!] 
   ON  TABLE1
   AFTER UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE([LastAccessed]) BEGIN
        IF EXISTS(SELECT * FROM inserted) BEGIN
            INSERT INTO [Table1_Hist](
                Action, Id, LastAccessed, Data1, Data2
            ) SELECT 
                'EDIT', Id, LastAccessed, Data1, Data2
            FROM deleted
        END ELSE BEGIN
            INSERT INTO [Table1_Hist](
                Action, Id, LastAccessed, Data1, Data2
            ) SELECT 
                'DELETE', Id, LastAccessed, Data1, Data2
            FROM deleted
        END
    END
END

This trigger will not copy row if LastAccessed and Data1 both change (which I want to). to solve this issue I can change the IF statement to 

IF UPDATE(Id) OR UPDATE(Data1) OR UPDATE(Data2) ... BEGIN

In this case it will work as intended

Question: As I have 34 columns in my table it is not easy to specify every column in IF statement. Is there any easier way of doing this?

Upvotes: 1

Views: 4256

Answers (2)

Jon Boulineau
Jon Boulineau

Reputation: 451

CREATE TRIGGER [TRIGGER!] 
   ON  TABLE1
   AFTER UPDATE
AS 
BEGIN

INSERT INTO Table1_Hist (
Action
Id
LastAccessed
Data1
Data2
)
SELECT 'UPDATE',d.ID,d.LastAccessed,d.Data1,d.Data2, etc., etc.
FROM DELETED d
INNER JOIN INSERTED i ON d.id = i.id
WHERE 
COALESCE(d.data1,'') <> COALESCE(i.data1,'') OR
COALESCE(d.data2,'') <> COALESCE(i.data2,'') OR
<repeat for every attribute EXCEPT LastAccessed>
END

This will also work for inserts or updates of multiple rows, whereas your method would potentially insert rows where the data did not actually change.

Also, you want to have separate UPDATE and DELETE triggers because your query should just dump the full row set from DELETED in case of an actual DELETE.

Upvotes: 0

Bill Hall
Bill Hall

Reputation: 586

Hopefully someone has a more elegant answer than this, but worst case you can generate that ugly IF statement for all 34 columns with a simple query:

SELECT CASE WHEN column_id = 1 THEN 'IF ' ELSE 'OR ' END + 'UPDATE(' + name + ')' 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') 
        AND name <> 'LastUpdated'
    ORDER BY column_id

Then you can just cut-and-paste the results for your IF statement... not an elegant solution, but it's quicker than typing it all by hand.

Upvotes: 3

Related Questions