Using a Trigger to GETDATE()

Question

I'm using a Trigger to update the Products table to the current date if the value for that column is null. I have everything yet, when I insert the new values into the table the DateAdded column for it says NULL instead of today's current date. I'm not exactly sure what's wrong and why it isn't working. Any help would be appreciated, Thanks!

IF OBJECT_ID('Products_INSERT') IS NOT NULL
DROP TRIGGER Products_INSERT

CREATE TRIGGER Products_INSERT
ON Products 
AFTER INSERT
AS
UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded = NULL;

Tag your question with the database you are using, which I presume is SQL Server. — Gordon Linoff, Mar 04 '18 at 16:53
Possible duplicate of [Not equal <> != operator on NULL](https://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — GSerg, Mar 04 '18 at 16:57

score 5 · Answer 1 · answered Mar 04 '18 at 16:57

I would expect the trigger to look like this:

CREATE TRIGGER INSERT_Products ON Products 
AFTER INSERT
AS BEGIN
    UPDATE Products
        SET DateAdded = GETDATE()
        FROM Products p JOIN
             Inserted i
             ON p.productid = i.productid;
END;

If you want to test that the value was not set in the insert:

CREATE TRIGGER INSERT_Products ON Products 
AFTER INSERT
AS BEGIN
    UPDATE Products
        SET DateAdded = GETDATE()
        FROM Products p JOIN
             Inserted i
             ON p.productid = i.productid
        WHERE DateAdded IS NOT NULL;
END;

That said, I would just use a default value --

create table Products (
    ProductId int identity primary key,
    . . . 
    DateAdded datetime not null default getdate()
);

Much simpler than a trigger, and this still lets you override the default with your own value.

score 3 · Answer 2 · answered Mar 04 '18 at 16:54

3

null isn't a value, it's the lack thereof. In order to check against it, you need to use the is operator, not the = operator:

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

answered Mar 04 '18 at 16:54

Mureinik

297,002
52
306
350

Using a Trigger to GETDATE()

2 Answers2