Trigger is not firing in SQL Server

Question

I've created the following trigger that should be looking for an update. My understanding is that an update creates an entry in the DELETED table and the INSERTED table. So I am unsure as to why the following isn't working. (It is checking to see if the value was changed from one to the other and if so, log it in a 2nd table).

CREATE TRIGGER [dbo].[captureNameChainToLocateComplete] 
ON [dbo].[tblLand]
AFTER UPDATE
AS 
BEGIN
    DECLARE @old AS varChar(50)
    DECLARE @new AS varChar(50)
    DECLARE @name AS varchar(50)
    DECLARE @now AS dateTime
    DECLARE @recordId AS varchar(50)

    SELECT @old = d.sSubdivision FROM DELETED d
    SELECT @new = i.sSubdivision FROM INSERTED i

    SELECT @name = (SELECT TOP 1 sHistoryUser 
                    FROM tblDocumentHistory 
                    ORDER BY dtHistoryDate DESC)
    SELECT @recordId = (SELECT TOP 1 iRecordId 
                        FROM tblDocumentHistory 
                        ORDER BY dtHistoryDate DESC)
    SELECT @now = (SELECT GETDATE())

    IF @old = 'NAME CHAIN' AND @new = 'LOCATE COMPLETE'
    BEGIN
        INSERT INTO nameChainLog (recDate, irecordId, userName) 
        VALUES (@now, @recordId, @name)
    END
    ELSE
    BEGIN
        INSERT INTO nameChainLog (recDate, irecordId, userName) 
        VALUES (@now, 'test', 'test')
    END
END

Edit: The second insert is just for troubleshooting to try to log when an else occurs.

Answer 1

you should try something like this, you need to cover the multiple updates

  CREATE TRIGGER [dbo].[captureNameChainToLocateComplete] 
ON [dbo].[tblLand]
AFTER UPDATE
AS 
BEGIN
    DECLARE @old AS varChar(50)
    DECLARE @new AS varChar(50)
    DECLARE @name AS varchar(50)
    DECLARE @now AS dateTime
    DECLARE @recordId AS varchar(50)

    -- Use a cursor to handle multiple rows in DELETED and INSERTED
    DECLARE cursorTrigger CURSOR FOR
    SELECT d.sSubdivision, i.sSubdivision
    FROM DELETED d
    JOIN INSERTED i ON d.irecordId= i.irecordId

    OPEN cursorTrigger
    FETCH NEXT FROM cursorTrigger INTO @old, @new

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @name = (SELECT TOP 1 sHistoryUser 
                        FROM tblDocumentHistory 
                        ORDER BY dtHistoryDate DESC)
        SELECT @recordId = (SELECT TOP 1 iRecordId 
                            FROM tblDocumentHistory 
                            ORDER BY dtHistoryDate DESC)
        SELECT @now = GETDATE()

        IF @old = 'NAME CHAIN' AND @new = 'LOCATE COMPLETE'
        BEGIN
            INSERT INTO nameChainLog (recDate, irecordId, userName) 
            VALUES (@now, @recordId, @name)
        END
        ELSE
        BEGIN
            INSERT INTO nameChainLog (recDate, irecordId, userName) 
            VALUES (@now, 'test', 'test')
        END

        FETCH NEXT FROM cursorTrigger INTO @old, @new
    END

    CLOSE cursorTrigger
    DEALLOCATE cursorTrigger
END