SQLTeam.com | Weblogs | Forums

Triggers - Help - School project

Hello guys, I'm having issues trying to implement an after insert triggers that respond to the following:

Whenever a new ride is inserted the ride repetitions must be created. Each ride has at
least one repetition with Repetition Date = Starting Date. For each repetition created,
the Repetition Date must be calculated automatically starting with Starting Date until
Finish Date, using the frequency attribute for the calculus. (INSERT)

I have 2 tables RIDE and RIDE_REPETITION. Each ride has as Start and End date and a frequency.
Based on the start and end dates I need to create the repetitions on the repetition table (just the Ride ID and Repetition Date).

Any clue how I should handle this?
Thanks!

Yes. Use a tally table to generate the repititions. A tally table is nothing but a table of sequential numbers. The first row contains 0; the second row contains 1; the next row 2; the next row 3; etc.

You can use that table to gen the dates (and/or times) between the Starting Date and the Finish Date. If you can provide specific CREATE TABLE definitions, I can give you more complete code.

Hello Scott, thanks a lot for your reply!!
Well the questions is that we are being asked to solve this using triggers....
About the Create table definitions, i'm sending if below:

/==============================================================/
/* Table: RIDES /
/
==============================================================*/
create table RIDES (
ID_RIDE numeric identity,
ID_USER numeric not null,
ID_CAR numeric not null,
START_CITY numeric not null,
FINISH_CITY numeric not null,
STARTING_DATE datetime not null,
FINISH_DATE datetime null,
FREQUENCY varchar(20) null
constraint CKC_FREQUENCY_RIDES check (FREQUENCY is null or (FREQUENCY in ('Daily','Working Days','Weekly','Monthly'))),
CAR_SHARING bit null,
PRICE money not null,
constraint PK_RIDES primary key nonclustered (ID_RIDE),
constraint FK_RIDES_CARS foreign key (ID_USER, ID_CAR)
references CARS (ID_USER, ID_CAR),
constraint FK_RIDE_START foreign key (START_CITY)
references CITIES (ID_CITY),
constraint FK_RIDE_FINISH foreign key (FINISH_CITY)
references CITIES (ID_CITY)
)
go

/==============================================================/
/* Table: REPETITIONS /
/
==============================================================*/
create table REPETITIONS (
ID_RIDE numeric not null,
ID_REPETITION int not null,
REPETITION_DATE datetime not null,
constraint PK_REPETITIONS primary key nonclustered (ID_RIDE, ID_REPETITION),
constraint FK_REPETITIONS_RIDES foreign key (ID_RIDE)
references RIDES (ID_RIDE)
)
go

Many thanks once again!!

The dbms is SQL Server, right?

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER RIDES_INSERT_TRIGGER
ON RIDES
AFTER INSERT
AS
SET NOCOUNT ON;
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number 
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
INSERT INTO REPETITIONS ( ID_RIDE, ID_REPETITION, REPETITION_DATE )
SELECT 
    i.ID_RIDE, t.number AS ID_REPETITION, DATEADD(DAY, t.number - 1, i.STARTING_DATE) AS REPETITION_DATE
FROM inserted i
INNER JOIN cte_tally1000 t ON t.number BETWEEN 1 AND DATEDIFF(DAY, STARTING_DATE, FINISH_DATE) + 1
GO

Hello Scott, thanks a lot! However this is a but different of what we learned on classes, i'm a bit affraid that professor will figure that i had some help on this....:wink:
Also I'm getting the following error:

Msg 207, Level 16, State 1, Procedure RIDES_INSERT_TRIGGER, Line 16 [Batch Start Line 12]
Invalid column name 'number'.

Completion time: 2021-12-07T01:01:03.4023343+00:00

Thanks once again!

I have no way of knowing what you learned in class, unless you tell me. I have corrected the code above: I accidentally wrote i.number instead of t.number.

Hey,
well we never used that table approach....any suggestion with a more simple archaic approach?
Many thanks once again!

Are you talking about the inserted table or the tally table?

Hmm, without using the inserted table, how in a trigger would you know which rows you had just inserted vs which rows were already in the table?

Without a tally table? No, I can't recommend a more archaic approach, it's been too long since I've done that. I'm used to using tally tables for all sorts of things.

tally table, is the first time i heard about it.
I understand and appreciate your help!
Many many thanks,