CODEWITHSUNDEEP
sqlsql-server
Logan
Logan

Reputation: 11

How to make an AFTER INSERT TRIGGER to change a date to getdate() if its null

I know you can set defaults and constraints on a column but its an assignment. This is what I have so far. 

ALTER TRIGGER Products_INSERT
    ON dbo.Products
    AFTER INSERT
AS BEGIN
    UPDATE dbo.Products
    SET Products.DateAdded = GETDATE()
    FROM products, inserted
    WHERE inserted.DateAdded = NULL
END

The problem is when I INSERT a row without specifying a a date it stays NULL and the TRIGGER never executes

Here is the question for reference.

Create a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table when a row is inserted that has no DateAdded specified (DateAdded is null).

Upvotes: 1

Views: 1479

Answers (5)

Klajdi Begeja
Klajdi Begeja

Reputation: 31

You should change the default of the column from is null to GETDATE(). So in every insert you made without the field you will have the date from the GETDATE(). Is no need for trigger to do that.

Upvotes: 1

Logan
Logan

Reputation: 11

Thanks for all the help I got it to work I was missing a JOIN between the inserted and products table.

ALTER TRIGGER Products_INSERT
    ON dbo.Products
    AFTER INSERT
AS BEGIN
    UPDATE dbo.Products
    SET Products.DateAdded = GETDATE()
    FROM Products p JOIN inserted i 
        ON p.ProductID = i.ProductID 
    WHERE p.DateAdded = NULL
END

Upvotes: 0

var_missmal
var_missmal

Reputation: 98

Almost there. You need to use IS NULL instead of = NULL in the WHERE clause.

EDIT: To integrate other suggestions... you also need to correlate the tables on their primary key, and should switch to an ANSI join. Table aliases also make it more readable. 

ALTER TRIGGER products_insert 
ON dbo.Products 
after INSERT 
AS 
  BEGIN 
      UPDATE dbo.Products 
      SET    p.DateAdded = Getdate() 
      FROM   products p 
             JOIN inserted i 
               ON p.PrimaryKey = i.PrimaryKey 
      WHERE  i.DateAdded IS NULL 
  END

Upvotes: 1

Asakuraa Ranger
Asakuraa Ranger

Reputation: 617

When Update TableA from TableB you no need to join TableA

try this trigger its may work

ALTER TRIGGER Products_INSERT
    ON dbo.Products
    AFTER INSERT
AS BEGIN
    UPDATE dbo.Products
    SET Products.DateAdded = CASE WHEN inserted.DateAdded IS NOT NULL THEN inserted.DateAdded else GETDATE() END --Check if date is null replace with getdate() stay still if it has a value
    FROM inserted
    WHERE dbo.Products.PK = inserted.PK --Primary Key
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Don't use a trigger for this. Just use a default value:

alter table dbo.Products alter column DateAdded datetime default getdate();

Upvotes: 0

Related Questions