CODEWITHSUNDEEP
sqlsql-servertriggerssql-insert
Abdallah
Abdallah

Reputation: 47

SQL Server 2012 AFTER INSERT trigger that replaces null value with current date

I need to create a trigger that inserts the current date for the DateAdded column of the Products table if the value for that column is null.

Here's what I've tried:

Use MyGuitarShop;
IF OBJECT_ID ('Products_INSERT') IS NOT NULL
    DROP TRIGGER Products_INSERT;
GO

CREATE TRIGGER Products_INSERT
    ON Products
    AFTER INSERT
AS
    UPDATE Products
    SET DateAdded = GETDATE()
    WHERE DateAdded IS NULL
        AND DateAdded IN (SELECT DateAdded FROM inserted);
GO

To test this I ran the following INSERT statement:

INSERT INTO Products
VALUES (4, 'AK-5300', 'Awesome Keyboard 5300',
        'This keyboard is so awesome, you just might freak!',
        699.99, 30.00, NULL)

After running the INSERT statement, the DateAdded column still says NULL.

Any help is appreciated.

Many thanks in advance!

Upvotes: 0

Views: 3399

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Your approach isn't the right approach. You should just use a default value for DateAdded. But, the problem with this query:

UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded IS NULL
    AND DateAdded IN (SELECT DateAdded FROM inserted);

Is that the two conditions are incompatible. You have DataAdded IS NULL fine. That is true. But almost any comparison to NULL is false, including the IN comparison. So for what you want to do, this should suffice:

UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded IS NULL;

You could write:

UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded IS NULL OR
      DateAdded IN (SELECT DateAdded FROM inserted);

I do note that the trigger is updating the same table being modified, which often leads to confusing code.

Upvotes: 1

Related Questions