Check for changes to an SQL Server table?

sql

checksums

change-detection

sql-server

byNikita Barsukov·Nov 28, 2024

To detect changes in your SQL Server table, use the power of triggers.

Here's an efficient implementation for an audit trigger:

CREATE TRIGGER dbo.TableAuditTrigger
ON dbo.YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO dbo.AuditLog (EventType, RecordID, ChangedData)
    SELECT CASE WHEN i.ID IS NOT NULL AND d.ID IS NULL THEN 'INSERT'  /* Fresh as a morning blossom */
                WHEN i.ID IS NOT NULL AND d.ID IS NOT NULL THEN 'UPDATE'  /* Been there, done that */
                WHEN i.ID IS NULL AND d.ID IS NOT NULL THEN 'DELETE'  /* Gone with the wind */
           END, COALESCE(i.ID, d.ID), COALESCE(i.YourColumn, d.YourColumn)
    FROM inserted i
    FULL OUTER JOIN deleted d ON d.ID = i.ID
END;

Just modify the AuditLog schema and the YourTable and YourColumn names as needed. No rocket science here, just a trigger that watches each INSERT, UPDATE, DELETE events, identifying the action type and the impacted data.

Altitude beyond triggers

Regular check-ups - checksums to the rescue

When triggers aren't possible due to the database being vendor-owned, try conducting check-ups with checksums. Using the BINARY_CHECKSUM function within CHECKSUM_AGG provides a snapshot identifier that alters with any change in your table. Here's a handy guide:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.YourTable WITH (NOLOCK); /* Check yourself before you wreck yourself */

Note: Use WITH (NOLOCK) sparingly to prevent table lock, but beware of the stray dirty reads it may cause.

Checksums - "Are you sure?"

Checksums are pretty efficient but not flawless. They might show false negatives if data changes are symmetric, tricking them. If your system is ultra-critical, bolster checksums with additional verification methods or more accurate change detection mechanisms.

SQL Server and the artifacts of change detection

SQL Server 2005 onward users have an extra perk - use the SQLDependency class to automagically get updates on changes in query results. Under the hood of this beauty, you'll find Query Notifications working tirelessly.

Ascending to advanced change tracking

MORE accuracy? We've got you

If you're in search of greater accuracy, consider Change Tracking or Change Data Capture (CDC). Although with some additional overhead, these in-built SQL Server mechanisms provide more detailed changes. Think about them as your cape in the superhero universe of enterprise applications.

Co-opt with vendors for a change alert system

Forge an alliance with your software vendors for an agreed alert system that catifies on data changes without altering the database structure. You can often score this by utilizing APIs or vendor-provided integrations.

Weighing the trade-offs

Balance the size of your tables and change frequency for optimized performance of your change detectors. Titanic-sized tables might require tough love like indexed views or batched checksum calculations.

API-oco Now - Limitless change detection

APIs could be your knight in shiny armor for a seamless bridge between table changes and external applications, without directly polling the database. Plus, it's more scalable and unobtrusive in a multi-service platform.

explain-codes / Sql / Check for changes to an SQL Server table?

Linked

How to test run an UPDATE statement in PostgreSQL?



How do I swap column values in sql server 2008?



Row-level trigger vs statement-level trigger



Is there a way to SELECT and UPDATE rows at the same time?



Postgresql - add boolean column to table set default

