Trigger question

[Deam]

I have to create a trigger on a table T1. This table has 3 fields. PersonID, StartDate and EndDate.When the user enters the StartDate the EndDate should automatically be calculated as 3 months from the StartDate. Any ideas ?? This is what I had, it does not do anything not even an error:

IF UPDATE(EndDate)
BEGIN
UPDATE t1
SET t1.EndDate = DateAdd(mm, +3, convert(datetime, convert(varchar(2), Month(t1.StartDate)) + '/01/' + convert(varchar(4), Year(t1.StartDate))))
FROM t1 INNER JOIN inserted i ON t1.PersonID = i.PersonID AND t1.StartDate = i.StartDate
WHERE DATEPART(hh, i.EndDate) = 0 AND
DATEPART(mi, i.EndDate) = 0 AND
DATEPART(ss, i.EndDate) = 0 AND
DATEPART(ms, i.EndDate) = 0
END

 

[tlbroadbent]

1) If the user upodates StartDate, you should test for that update rather than testing to see if EndDate was updated.

2) If EndDate Is Null on the inserted row, all of the Datepart functions in the Where clause will return NULL rather than zero so the update wil no find any rows to update.

Try this.

IF UPDATE(StartDate)
BEGIN
UPDATE t1
SET t1.EndDate =
DateAdd(mm, +3, convert(datetime,
convert(varchar(2), Month(t1.StartDate)) +
'/01/' + convert(varchar(4),
Year(t1.StartDate))))
FROM t1 INNER JOIN inserted i
ON t1.PersonID = i.PersonID
--Is startdate required to uniquely identify a row?
--If PersonID is unique then remove the next line.
AND t1.StartDate = i.StartDate
WHERE i.EndDate Is Null
END Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.