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).
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
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....
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'.
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.
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.
