469,610 Members | 2,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Trigger to populate another table.

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.
Jul 20 '05 #1
3 3541
This is actually a simple query problem. You don't need the extra table
and trigger to solve it.

To search for events on a particular day (@dt):

SELECT *
FROM Events
WHERE @dt >=date
AND @dt < DATEADD(DAY,duration,date)

Better still, put the start and end dates of the event in the table
rather than a date and a duration (this should optimize much better than
the above):

SELECT *
FROM Events
WHERE @dt >=start_date
AND @dt <=end_date

If you really wanted to expand the event as a row for each day then I
would do it in a view rather than create a redundant table. Use a
calendar table:

http://www.google.com/groups?selm=R8...40giganews.com

SELECT E.title, C.caldate
FROM Calendar AS C
JOIN Events AS E
ON C.caldate >= date
AND C.caldate < DATEADD(DAY,duration,date)

Hope this helps.

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #2
On 19 Nov 2004 03:11:08 -0800, Luklrc wrote:
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


Hi Luklrc,

Instead of filling a second table with derived data, why not consider
changing the query so that a 3-day event starting 15 nov will turn up when
searching for 16 nov.

If you post table structure (as CREATE TABLE statements, including all
constraints and properties but excluding irrelevant columns), sample data
(as INSERT statements) and expected results, then we should be able to get
this sorted out for you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3
Thanks David

I tried that view joining the events table and a calaner table and it
works like a charm!

Thanks again,

Luke

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Martin | last post: by
8 posts views Thread by Remove the obvious for replies | last post: by
2 posts views Thread by gustavo_randich | last post: by
2 posts views Thread by mike | last post: by
3 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.