Category: SQL Azure Trigger

The below trigger monitors a table called t001Table4Monitoring and four particular fields / 3 date fields and one integer field. It monitors the trigger table and compares new values to old values using simple subtraction. As a comparison between null and a value will always return null we need to catch situations were a field is changed from null or to null hence the ISNULL section. The record has a bit field that is updated to True if change is detected.

This code was created with the help of GPT-4

CREATE TRIGGER [dbo].[trg_Update_t001Table4Monitoring]
ON [dbo].[t001Table4Monitoring]
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Date01Monitor) OR UPDATE(Date02Monitor) OR UPDATE(Date03Monitor) OR UPDATE(IntegerMonitor)
    BEGIN
        -- Get old and new values
        DECLARE @old_Date01Monitor DATE, @new_Date01Monitor DATE;
        DECLARE @old_Date02Monitor DATE, @new_Date02Monitor DATE;
        DECLARE @old_Date03Monitor DATE, @new_Date03Monitor DATE;
        DECLARE @old_IntegerMonitor INT, @new_IntegerMonitor INT;

        SELECT @old_Date01Monitor = Date01Monitor, @old_Date02Monitor = Date02Monitor, @old_Date03Monitor = Date03Monitor, @old_IntegerMonitor = IntegerMonitor FROM deleted;
        SELECT @new_Date01Monitor = Date01Monitor, @new_Date02Monitor = Date02Monitor, @new_Date03Monitor = Date03Monitor, @new_IntegerMonitor = IntegerMonitor FROM inserted;

        -- Check if values have changed
        IF (ISNULL(@old_Date01Monitor, '1900-01-01') <> ISNULL(@new_Date01Monitor, '1900-01-01')) OR 
           (ISNULL(@old_Date02Monitor, '1900-01-01') <> ISNULL(@new_Date02Monitor, '1900-01-01')) OR 
           (ISNULL(@old_Date03Monitor, '1900-01-01') <> ISNULL(@new_Date03Monitor, '1900-01-01')) OR 
           (ISNULL(@old_IntegerMonitor, 0) <> ISNULL(@new_IntegerMonitor, 0))
        BEGIN
            UPDATE dbo.t001Table4Monitoring
            SET RecordChanged = 1
            FROM dbo.t001Table4Monitoring t
            INNER JOIN inserted i ON t.pkid = i.pkid
        END
    END
END;