CODEWITHSUNDEEP
triggerssql-server-2008-r2null
igor.sinkovec
igor.sinkovec

Reputation: 23

SQL Server AFTER UPDATE trigger not working when updating value from NULL

For reasons too silly to explain I have three columns in a table which hold date and time values. One column only holds the date, the second only holds the time, and the third holds a DATETIME value. Looks like this:

OPPORTUNITYID | ... | ProductionDate | ProductionTime | PRODUCTIONDATETIME
-------------------------------------------------------------------------------
091798-324971 | ... | 12-07-2014     | 11:30 AM       | 2014-07-12 11:30:00:000

Then I have a trigger that keeps these values in sync regardless of which is being updated. This is (part of) the trigger:

CREATE TRIGGER [dbo].[TBL_OPPORTUNITY_DUEDATES_TRU]
ON  [dbo].[TBL_OPPORTUNITY]
AFTER UPDATE
AS

BEGIN
    SET NOCOUNT ON;

    IF UPDATE ( PRODUCTIONDATETIME )
    BEGIN

        UPDATE TBL_OPPORTUNITY
        SET ProductionDate = CONVERT(VARCHAR(10), PRODUCTIONDATETIME, 105)
        , ProductionTime = REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(VARCHAR, PRODUCTIONDATETIME,100), 7)), 7), 'AM', ' AM'), 'PM', ' PM')
        WHERE OPPORTUNITYID IN ( SELECT i.OPPORTUNITYID FROM inserted i
            INNER JOIN deleted d ON i.OPPORTUNITYID = d.OPPORTUNITYID
            WHERE NOT i.PRODUCTIONDATETIME = d.PRODUCTIONDATETIME
            AND NOT ( i.PRODUCTIONDATETIME = '' OR i.PRODUCTIONDATETIME IS NULL )
        );
    END

    IF ( UPDATE ( ProductionDate ) OR UPDATE ( ProductionTime ) )
    BEGIN

        UPDATE TBL_OPPORTUNITY
        SET PRODUCTIONDATETIME = CONVERT(DATETIME, ProductionDate + ' ' + ProductionTime, 105)
        WHERE OPPORTUNITYID IN ( SELECT i.OPPORTUNITYID FROM inserted i
            INNER JOIN deleted d ON i.OPPORTUNITYID = d.OPPORTUNITYID
            WHERE ( NOT i.ProductionDate = d.ProductionDate
                OR NOT i.ProductionTime = d.ProductionTime )
            AND NOT ( i.ProductionDate = '' OR i.ProductionDate IS NULL )
            AND NOT ( i.ProductionTime = '' OR i.ProductionTime IS NULL )
        );

        UPDATE TBL_OPPORTUNITY
        SET PRODUCTIONDATETIME = CONVERT(DATETIME, ProductionDate + ' 12:00:00', 105)
        WHERE OPPORTUNITYID IN ( SELECT i.OPPORTUNITYID FROM inserted i
            INNER JOIN deleted d ON i.OPPORTUNITYID = d.OPPORTUNITYID
            WHERE ( NOT i.ProductionDate = d.ProductionDate
                OR NOT i.ProductionTime = d.ProductionTime )
            AND NOT ( i.ProductionDate = '' OR i.ProductionDate IS NULL )
            AND ( i.ProductionTime = '' OR i.ProductionTime IS NULL )
        );

    END

END
GO

The trigger works as expected whenever any of the values are updated. However, the trigger fails (as in doesn't make any changes) if a value is being updated from NULL, or in other words, the old value in a column was NULL and the new value is, for example '02-03-2014'.

Why is that?

The server is Microsoft SQL Server 2008 R2.

Thank you for any clues.

Upvotes: 2

Views: 4477

Answers (2)

susyc
susyc

Reputation: 1

Do this for every column you are tracking

    DECLARE @ProductionDate Date;     
    DECLARE @ProductionDateOld Date;
    DECLARE @ProductionDateInd bit = 0;  // you can use the indicator later to determine which field changed. if = 0 it didn't change, if 1 it did change
    DECLARE @ProductionDateTime DateTime;     
    DECLARE @ProductionDateTimeOld DateTime;
    DECLARE @ProductionDateTimeInd bit = 0;

    SELECT @ProductionDate=ProductionDate FROM inserted i;
    SELECT @ProductionDateOld = d.ProductionDate FROM deleted d;
    if @ProductionDateOld <> @ProductionDate 
        @ProductionDateInd = 1;
    if @ProductionDateOld IS NULL AND @ProductionDate IS NOT NULL 
        @ProductionDateInd = 1; 

SELECT @ProductionDateTime=ProductionDateTime FROM inserted i;
SELECT @ProductionDateTimeOld = d.ProductionDateTime FROM deleted d;
if @ProductionDateTimeOld <> @ProductionDateTime 
  @ProductionDateTimeInd = 1;
if @ProductionDateTimeOld IS NULL AND @ProductionDateTime IS NOT NULL 
   @ProductionDateTimeInd = 1;

Then check if a value changed (I do this to only write to history/audit table if there truly is a change)

if (@ProductionDateInd = 1 OR
@ProductionDateInd = 1)

INSERT INTO TBL_OPPORTUNITY_DUEDATES_TRU (
ProductionDate,
ProductionDateInd,
ProductionDateTime,
ProductionDateTimeInd,
.
.
.
)

VALUES (
@ProductionDate,
@ProductionDateInd,
@ProductionDateTime,
@ProductionDateTimeInd,
.
.
.
)

Upvotes: 0

Muqo
Muqo

Reputation: 414

As it is written, the trigger has to ignore rows where the old values are NULL because an equality or inequality involving NULL can never evaluate to true. A WHERE condition such as NOT i.ProductionDate = d.ProductionDate is never going to return any rows if d.ProductionDate is NULL, no matter what i.ProductionDate is. You would need to explicitly check the possibility that d.ProductionDate IS NULL to handle the cases where d.ProductionDate has no value.

Upvotes: 1

Related Questions