SQL Insert Datediff as table column upon date insertion

SQL Server Administration
1

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!

2

Where is the trigger supposed to find StartDate and EndDate? (Hint, you need to refer to the pseudo-table 'inserted')

INSERT INTO CurrentApplication
	NoOfDays AS Datediff(day, StartDate, EndDate)

is not a valid SQL statement. Perhaps you are trying to update a row instead?

That's not the only problem here. For example, what if I run this statement with your trigger:

INSERT INTO CurrentApplication (Startdate, EndDate, StaffId) values
(getdate(), getdate(), 1),
(getdate(), getdate(), 2)

Your trigger is not set up to handle multiple rows of data.

Please read Create Trigger and examples.

3

You don't need a trigger. You can use a computed column instead.

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