473,324 Members | 2,179 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 3711
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: June Moore | last post by:
Hi all, I have two tables tbl_A and tbl_B. Table Definitions: tbl_A (field_1, field_2, field_3) tbl_B (field_1, field_2) I would like to populate tbl_B when a record is inserted into tbl_A....
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
8
by: Remove the obvious for replies | last post by:
I am using DB2 8.1.4 with SDE and Info Integrator. I am trying to create a trigger to populate a spatial table based on values from an attribute table. However, it appears to not recognize the CASE...
0
by: gustavo_randich | last post by:
Hello, I'm looking for a DB2 workaround on a topic already solved in Oracle: the problem of mutating tables (which states that a trigger action cannot read the triggering table's data). Yes, I...
2
by: gustavo_randich | last post by:
Hi :-) I'm porting a project from Oracle to DB2 and now I'm trying to avoid error SQL0746N in a trigger which reads the same table in which the trigger is defined. Below is Oracle's...
2
by: mike | last post by:
I have a SQL based ERP package. Now I am having an issue on a particular table of people deleting a record (in the ERP system) and no one having any idea of who did it or when. So I thought I...
6
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that does this: 1. When a new record in a table is...
2
by: mike | last post by:
I'm looking for an efficient way to populate derived columns when I insert data into a table in SQL Server. In Informix and PostgreSQL this is easily done using the "for each row..." syntax, but...
3
by: lenygold via DBMonster.com | last post by:
Thank you very much SERGE for your help. I found example in Graeme Birchall COOKBOOK wich i think exactly what i need for SQL check in triggers: • User query joins to table function - sends...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.