May 26, 2008 at 6:23 pm
Hi,
I have 3 date fields (Date1, Date2, Date3) and 1 final date field (Date4).
I would like to write an update trigger that:
1. Updates Date4 if any of Date1, Date2, Date3 are updated.
2. The value of Date4 would be the largest date of Date1, Date2, Date3.
3. Date4 would only have a value if all Date1, Date2, Date3 were not null. If any of these fields were null then Date4 would be null.
Is there a simple function to use for comparing dates, and does anyone have any suggestions on the code to use for the update trigger (as I am new to this)?
Thanks
May 26, 2008 at 7:55 pm
This should do it:
Create Trigger trgJuggleDates
On tablename
After Update
AS
IF Update(Date1) OR Update(Date2) or Update(Date3)
BEGIN
Update T
Set Date4 =
CASE When (Date1+Date2+Date3) is Null Then Null
ELSE ( Case When Date1 > Date2
Then (Case When Date1 > Date3 Then Date1 Else Date3 End)
Else (Case When Date2 > Date3 Then Date2 Else Date3 End)
End )
END
From tablename T
Inner Join inserted I ON i.PrimaryKey = T.PrimaryKey
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2008 at 11:33 pm
Thanks
May 27, 2008 at 6:43 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply