SQL Server INSERT trigger to set initial dates syntax

Question

I've looked at some posts regarding updating on an INSERT trigger. My question is similar, but I wary of my syntax on the UPDATE statement, especially not understanding a table that keeps getting referred to as INSERTED.

The trigger code will update some date column values on INSERT only (and thanks to some code I've found here from one of you gurus, there is logic to handle the 'shipdueDate' column).

There is already an incremented value for the Invoice number based on an Identifier setting, so when a new row is created, the invoice number is incremented by 1. However, in order to make it easier for an admin in the office to add invoice data, I thought placing some initial values in the orderdate, shipduedate, and dateinvoiced fields would allow easier data entry.

This is the code I've come up with

   CREATE TRIGGER Invoices_SetInitialDates ON dbo.Invoices FOR INSERT
   AS 
   BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
  SET NOCOUNT ON;
      DECLARE @fromDate DATETIME
  DECLARE @shipDate DATETIME
  DECLARE @daysToAdd int
    -- We promise a 7 day turn-around on orders, so it has to 
    -- ship on the 8th day
  SELECT @fromDate = CURRENT_TIMESTAMP, @DaysToAdd = 8
  SET datefirst 1

    -- code from stackoverflow to calculate the shipduedate based
    -- on week days. It doesn't have any logic for holidays, but it
    -- provides the Admin initial values to work with, and that she 
    -- can change in the new record.
      SELECT @shipDate =
        DATEADD(day,
        @daysToAdd%5 +
         CASE DATEPART(weekday,@fromDate) + @daysToAdd%5
           WHEN 6 THEN 2
           WHEN 7 THEN 1
         ELSE 0 END,
         DATEADD(week,@daysToAdd/5,@fromDate)
       )

       -- UPDATE statement for trigger here. I'm not sure
       -- here about a table called INSERTED, or if this will
       -- simply update the newly added record. This is where
       -- I believe I need guidance.
         UPDATE dbo.Invoices SET DateOrdered = @fromDate
         ,ShipDueDate = @shipDate
         ,DateInvoiced = @fromDate

       END
       GO

Thanks in advance. This is a great site with a lot of SQL Server talent.

Answer 1

Let me fisrt explain about inserted (and deleted). In a trigger youhavea availabel two tables which contain the data you are going to insert or delete. In an update , the old values are in deleted and the new values are in inserted. In a delete only deleted is populated. In an insert only inserted is populated. NOw the key thing to remember is that you can never rely on a trigger to only have one record in this inserted or deleted. If you inserted 100 records in one insert statement, then there are 100 records in inserted. Therefore code will not work properly if you assume only one record. A clue is that you shoudl not be setting a value from inserted to a scalar variable.

Your trigger needs to join to inserted in the update statment as right now it would update every record in the table with the new dates.

something like:

UPDATE inv
SET DateOrdered = @fromDate          
    ,ShipDueDate = @shipDate          
    ,DateInvoiced = @fromDate  
From dbo.Invoices inv
join inserted i on inv.id = i.id

Answer 2

If you're concerned only about INSERT, then there's absolutely no need for a trigger - just define defaults for your columns!

ALTER TABLE dbo.Invoices
ADD CONSTRAINT DF_Invoices_DateOrdered DEFAULT (GETDATE()) FOR DateOrdered

ALTER TABLE dbo.Invoices
ADD CONSTRAINT DF_Invoices_DateInvoiced DEFAULT (GETDATE()) FOR DateInvoiced

The ShipDueDate with its calculation logic is a bit of a trickier business - but maybe you can simplify that so you can define it as a default constraint, too.

Default constraints provide values to your columns upon INSERT, but only if the INSERT statement doesn't explicitly set those columns to some other value.