Multi Row Triggers - adding multiple rows
-
Is this possible?
I have a database to record room bookings...
2 tables...
CREATE TABLE [tblBooking] (
[idBooking] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[Length] [smallmoney] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [tblDiary] (
[idDiary] [int] IDENTITY (1, 1) NOT NULL ,
[idBook] [int] NOT NULL ,
[dDate] [datetime] NOT NULL ,
)
GO
When a record is added into this tblBooking it adds corresponding rows into tblDiary, ignoring non-workdays.
At the moment a trigger takes the startdate and length from tblBooking and uses a function to return a table containing tblBooking.Length rows.
CREATE TRIGGER BookingAdded ON dbo.tblBooking FOR INSERT
AS
DECLARE @HID INT
DECLARE @LEN INT
DECLARE @SDDATETIME
SET @HID = (SELECT idBooking FROM INSERTED)
SET @LEN = (SELECT Length FROM INSERTED)
SET @SD = (SELECT StartDate FROM INSERTED)
INSERT INTO [Test].[dbo].[tblDiary]
( [idBook]
, [dDate])
SELECT @HID, DATE FROM fn_Calendar_GetArrayDates(@SD, @LEN)
This works fine for a single row insert but fails miserably on multi row inserts.
I can't get my head around how to join a function to the inserted table using an IN(?) clause. Is that even possible?
Colin
-
No you cannot use the function in that way.
Why not take the query from the function and join it to the INSERTED table?
What code is in the function?
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for confirming that functions don't work that way
The function is
Create function fn_Calendar_GetArrayDates
(
@SDATE DATETIME,
@DURATION INT
)
RETURNS @DATES TABLE
(
DATE DATETIME
)
AS
BEGIN
DECLARE @EDATE DATETIME
SET @EDATE =
(SELECT c.Date
FROM FLITE_Calendar c
WHERE
c.WorkDay = 1
AND @duration = (
SELECT COUNT(*)
FROM FLITE_Calendar c2
WHERE c2.Date >= @Sdate
AND c2.Date <= c.Date
AND c2.WorkDay=1
)
)
INSERT @DATES ( DATE)
SELECT DATE
FROMFLITE_CALENDAR
WHEREDATE BETWEEN @SDATE AND @EDATE
ANDWorkDay=1
RETURN
END
-
You could leave the first part in the function and put the second in the join like this
CREATE FUNCTION dbo.fn_Calendar_GetEndDate (@SDATE DATETIME,@DURATION INT)
RETURNS datetime
AS
BEGIN
DECLARE @EDATE DATETIME
SELECT @EDATE = c.[Date]
FROM FLITE_Calendar c
WHERE c.WorkDay = 1
AND @DURATION = (SELECT COUNT(*)
FROM FLITE_Calendar c2
WHERE c2.DATE >= @SDATE
AND c2.[DATE] <= c.[DATE]
AND c2.WorkDay=1)
RETURN @EDATE
END
INSERT INTO [Test].[dbo].[tblDiary]
SELECT i.idBooking,c.[DATE]
FROM [INSERTED] i
INNER JOIN FLITE_CALENDAR c
ON c.[DATE] BETWEEN i.StartDate AND dbo.fn_Calendar_GetEndDate(i.StartDate,i.Length)
AND c.WorkDay = 1
I would be worried about performance though
Far away is close at hand in the images of elsewhere.
Anon. -
This is quite a small system in fact and, apart from my bulk insert to import existing data, the likelyhood of their being bulk inserts is small.
Thanks for you help.
Colin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply