CODEWITHSUNDEEP
sqlsql-servertriggerssql-server-2012
user4840466
user4840466

Reputation:

SQL Assign datediff to table column in insert trigger

Table

CREATE TABLE CurrentApplication
(
    StartDate       datetime        NOT NULL,
    EndDate         datetime        NOT NULL,
    NoOfDays        integer,
    StaffID         integer         NOT NULL,
    AppStatus       varchar(30)     NOT NULL    DEFAULT 'PENDING'
)

Insert Trigger

CREATE TRIGGER InsertNoOfDays ON CurrentApplication
AFTER INSERT
BEGIN
INSERT INTO CurrentApplication
    NoOfDays AS Datediff(day, StartDate, EndDate)
END

I have NoOfDays column which should hold the DateDiff between StartDate and EndDate and the value should be inserted whenever a new record is inserted into the table. How do I write the trigger and do it? I've tried but my trigger doesn't work. Thanks!

Upvotes: 0

Views: 517

Answers (3)

Nachi
Nachi

Reputation: 114

Since not eligible to add comments, adding as a new answer.

Adding to @Gordon Linoff's answer, just substituted column names instead of periods to see if that caused the error you getting. I didnt get that error. Not sure if there is anything with SQL 2012.

But, this works (in SQL 2014):

CREATE TRIGGER InsertNoOfDays ON dbo.CurrentApplication
INSTEAD OF INSERT AS
BEGIN
    INSERT INTO CurrentApplication(StartDate, EndDate, NoOfDays, StaffID)
        SELECT  StartDate
               ,EndDate 
               ,Datediff(dd, StartDate, EndDate)
               ,StaffId
        FROM inserted as i;
END;

Upvotes: 0

Nachi
Nachi

Reputation: 114

One thing you can do is add a computed column instead of actual column as NoOfdays.

There are benefits and drawbacks in adding a computed column.

You don't have have a trigger and calculate it. Saves on writes. This also means sometimes you cannot index on that column and reads may have an impact.

Here is the documentation from Microsoft on computed columns (documentation says it is only on SQL 2016 onwards): Specify Computed Columns in Tables

But, here is sample in SQL 2014 that worked for me (I think it works in SQL 2012 also)

use tempdb
GO
/*
    Wors in version:

    Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Standard Edition (64-bit
*/
IF OBJECT_ID('tempdb.dbo.CurrentApplication') IS NULL
    CREATE TABLE dbo.CurrentApplication
    (
        StartDate       datetime        NOT NULL,
        EndDate         datetime        NOT NULL,
        NoOfDays        AS (datediff(dd, StartDate, EndDate)) PERSISTED,
        StaffID         integer         NOT NULL,
    )

INSERT INTO dbo.CurrentApplication(StartDate, EndDate, StaffId)
SELECT TOP 10 
         StartDate   =   DATEADD(dd, object_id, '1/1/2017')
        ,EndDate     =   DATEADD(dd, object_id*object_id, '1/1/2017')
        ,StaffId =   object_id
FROM    sys.objects 
WHERE   object_id < 300
order by Object_id

SELECT * FROM CurrentApplication

IF OBJECT_ID('tempdb.dbo.CurrentApplication') IS NOT NULL
    DROP TABLE dbo.CurrentApplication

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

One method is to use an INSTEAD OF trigger:

CREATE TRIGGER InsertNoOfDays ON CurrentApplication
INSTEAD OF INSERT AS
BEGIN
    INSERT INTO CurrentApplication( . . ., NoOfDays)
        SELECT . . ., 
               Datediff(day, StartDate, EndDate)
        FROM inserted;
END;

Upvotes: 1

Related Questions