CODEWITHSUNDEEP
sqlsql-servertriggers
beginerdeveloper
beginerdeveloper

Reputation: 845

Update Trigger not working when update record have some field is null

My Update Trigger :

    declare @NewValue NVARCHAR(4000) = '';
        declare @OldValue NVARCHAR(4000) = '';

    select @OldValue =''
                + CASE WHEN i.Name <> d.Name THEN '"Name": "'+ d.Name +'", ' ELSE '' END+
                + CASE WHEN i.Address1 <> d.Address1 THEN '"Address1": "'+ d.Address1 +'", ' ELSE '' END+
                '' FROM 
                inserted i
                JOIN deleted d on (i.AccountId = d.AccountId) ;

    select @NewValue = ''
                + CASE WHEN d.Name = NULL OR i.Name <> d.Name THEN '"Name": "'+ i.Name +'", ' ELSE '' END+
                + CASE WHEN d.Address1 = NULL OR i.Address1 <> d.Address1 THEN '"Address1": "'+ i.Address1 +'", ' ELSE '' END+
                '' FROM 
                inserted i
                JOIN deleted d on (i.AccountId = d.AccountId) ;

if @NewValue <> '' AND @OldValue <> '' AND @NewValue <> @OldValue
            INSERT INTO Auditlog (OtherId, TableName, ActionDate, ActionBy, Operation, ActionType)
                    SELECT 
                        i.AccountId, 'Accounts', getdate(), '', 'Update', '' 
                    FROM 
                        inserted i

            INSERT INTO AuditlogDetails (AuditlogId, OldValue , NewValue)
                    values (IDENT_CURRENT('dbo.Auditlog'), N'[{'+ @OldValue +'}]', N'[{'+ @NewValue +'}]')

But this trigger just insert Table AuditlogDetails and @NewValue is NULL: for example i have a record name = "abc", address1 = NULL when i update that record Address1 = "adasf" then @NewValue is NULL i expected that @NewValue is [{"Address1": "adasf"}] some one help me pls

Upvotes: 0

Views: 376

Answers (2)

Adeel Warraich
Adeel Warraich

Reputation: 51

you can modify your condition like this

(old.name != new.name) OR (old.name is NULL) OR (new.name is NULL)

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Change your NULL comparison

select @NewValue = ''
                + CASE WHEN d.Name = NULL OR i.Name <> d.Name THEN '"Name": "'+ i.Name +'", ' ELSE '' END+
                + CASE WHEN d.Address1 = NULL OR i.Address1 <> d.Address1 THEN '"Address1": "'+ i.Address1 +'", ' ELSE '' END+
                '' FROM 
                inserted i
                JOIN deleted d on (i.AccountId = d.AccountId) ;

In the above lines you compared d.Name = NULL and d.Address1 = NULL. Null is something that we don't know. So you can't compare NULL with equality symbol.

Ex: 

Declare @var varchar(20) = NULL;
IF (@var = NULL)
BEGIN 
  PRINT 'Equals work'
END

IF (@var IS NULL)
BEGIN
    PRINT 'IS NULL WORK'
END

But the above behavior will change, when we use the property SET ANSI_NULLS (Transact-SQL)

Change it to d.Name is NULL instead. d.Address1 is NULL

Upvotes: 1

Related Questions