February 22, 2011 at 5:33 pm
I have a "TimeSlot" table. I created a trigger to prevent overlaps. When I try to do updates that span multiple rows, I get an odd error back. I must not have this coded correctly for a multiple row scenario. Can anyone tell me what I did wrong?
Here's what the table looks like:
CREATE TABLE TimeSlot (
TimeSlotId int identity(1,1)
,StartDateTime DateTime2
,StopDateTime DateTime2
,AvailableSlots int
)
Here's the trigger:
CREATE TRIGGER [dbo].[TimeSlot_PreventOverlap]
ON [dbo].[TimeSlot]
AFTER INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--* Prevent overlapping dates
IF EXISTS(
SELECT *
FROM TimeSlot ps
JOIN Inserted i ON i.EndDateTime >= ps.StartDateTime AND i.StartDateTime <= ps.EndDateTime
WHERE NOT (i.EndDateTime=ps.EndDateTime AND i.StartDateTime=ps.StartDateTime)
)
BEGIN
RAISERROR(N'TimeSlots cannot overlap', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
--* Prevent zero slot shifts
IF NOT (SELECT AvailableSlots FROM inserted) > 0
BEGIN
RAISERROR(N'TimeSlots must be greater than 0', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
Here's the error:
Msg 512, Level 16, State 1, Procedure ProctorSlot_PreventOverlap, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Thanks!
.
February 22, 2011 at 7:58 pm
When more than one record is inserted or updated in a single transaction, the SELECT statement in the line of code that reads:
IF NOT (SELECT AvailableSlots FROM inserted) > 0
will return multiple rows. This should probably be
IF NOT (SELECT COUNT(AvailableSlots) FROM inserted) > 0
instead.
February 22, 2011 at 8:16 pm
Yes, that was exactly the problem. That's what I meant to do in the first place.
Thank you very much HappyCat59!
.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply