Multi-user trigger 1

[CdnRissa]

I've searched for an answer to this and I've not found any specifics.

I have a table for which I want to keep a history of changes. I've created a history table that includes all the fields in the base table, as well as HistId (identity), HistCreateDt (default GetDate()), and Action fields.

In the Insert and Update triggers on the base table, I insert the record from Inserted into the History table, adding the appropriate 1-character Action (I or U).

So far, so good. However, I have not been able to figure out how to ensure that only the record that fired the trigger gets inserted into the history table.

I've seen several comments that suggest making it able to handle changes to more than one record, but how exactly?

Any help on this would be most gratefully received.

Cheers...

 

[bborissov]

CREATE TRIGGER TrackChanges ON SomeTable
       FOR INSERT, DELETE, UPDATE
AS
 BEGIN
     ---- Insert newly created records
     INSERT INTO HistoryTable (field list here)
     SELECT (some fields from INSERTED), "Insert" AS Action
     FROM INSERTED
     LEFT JOIN DELETED ON INSERTED.PK = DELETED.PK
     WHERE Deleted.PK IS NULL

     ---- Insert deleted records
     INSERT INTO HistoryTable (field list here)
     SELECT (some fields from DELETED), "Delete" AS Action
     FROM DELETED
     LEFT JOIN INSERTED ON INSERTED.PK = DELETED.PK
     WHERE INSERTED.PK IS NULL
    
     ---- Insert Updated records
     INSERT INTO HistoryTable (field list here)
     SELECT (some fields from DELETED), "Update" AS Action
     FROM INSERTED
     INNER JOIN DELETED ON INSERTED.PK = DELETED.PK
    /* If you want to track only some fields changes
       you could add:
     WHERE Iserted.SomeField  <> Deleted.SomeField OR
           Iserted.OtherField <> Deleted.OtherField ...
    */
 END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CdnRissa]

Thank you, Borislav!

Much appreciated.

Cheers....