Hi.
I have a problem I hope someone can help me with.
I have a database of events. Each event has a date and a duration
(days).
What I need to do is to be able to display search for events on some
web pages. My problem is that if an event is on say 15 nov 2004 and
has a duration of 3 days, at the moment if someone searches for the
16th nov 2004 that event wont show up. To solve this what I have tried
to do is put a trigger on my events table that populates another table
each time a record is inserted. In the other table (orderedevents) an
entry is added for each day specified in the duration field. I can the
query this table for the search. For example if this is added into the
events table
Title: Event1
Date: 15/11/2004
Duration: 3
Then this would be added to the orderedevents table
Title: Event1
Date: 15/11/2004
Duration: 3
Title: Event1
Date: 16/11/2004
Duration: 3
Title: Event1
Date: 17/11/2004
Duration: 3
This is what I have got so far. Someone kindly helped me with this
before but they had developed it on MySql, I'm using Sql Server and it
doesn't seem to work. All it does is add a blank row to OrderedEvents.
Can anyone suggest any amendments that will make it work on Sql
Server?
CREATE Trigger TriggerInsertOrderedEvents
ON events
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @CountDuration int,
@FixedDuration int,
@eventdate datetime,
@EventTitle varchar
SET @CountDuration = 0
SET @EventTitle = (SELECT EventTitle FROM inserted)
SET @FixedDuration = (SELECT EventDuration FROM inserted)
SET @EventDate = (SELECT EventDate FROM inserted)
WHILE (@CountDuration <= @FixedDuration - 1)
BEGIN
INSERT OrderedEvents (Eventdate,EventDuration,EventTitle)
VALUES(dateadd(day,@CountDuration,@EventDate),@Fix edDuration,@EventTitle)
SET @CountDuration = @CountDuration + 1
END
SET NOCOUNT OFF
Thanks in advance.