insert with condition question

I have tables of Vacations and VacationItems. Each Vacation has an owner and
is made up of one or more Vacation items. Vacation items have a startDate an
d
an endDate. A Vacation can only be inserted if none of its Vacation items
clash with any other Vacation items in Vacations owned by the user i.e a
user cannot book a Vacation on days he has already booked another Vacation o
n
I assume that I would wrap up each of the inserts in a transaction and if
any failed I would roll back the transaction.
What I want to know, at the moment, is how I would do an insert for a
Vacation item that would fail if the above condition had not been met
e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1,
01/01/05, 01/05/05)
WHERE
……..
Script
CREATE TABLE [dbo].[Vacation] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[VacationItem] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[VacationID] [int] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Vacation] ADD
CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VacationItem] ADD
CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VacationItem] ADD
CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
(
[VacationID]
) REFERENCES [dbo].[Vacation] (
[ID]
)
GOYou could use and INSTEAD OF INSERT trigger to check if the date range was
valid before performing the inserts.
--
Adam J Warne, MCDBA
"John" wrote:

> I have tables of Vacations and VacationItems. Each Vacation has an owner a
nd
> is made up of one or more Vacation items. Vacation items have a startDate
and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1
,
> 01/01/05, 01/05/05)
> WHERE
> ……..
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||Hi John
1. U could place a check for existence/validity just before doing an insert
into vacation item table & take suitable action ([insert and continue] or
[rollback and return error] or [bypass]).
2. U Can also think using IO triggers.. but again the above chk will be done
here
I would prefer the 1st suggestion
Rgds, Rakesh
"John" wrote:

> I have tables of Vacations and VacationItems. Each Vacation has an owner a
nd
> is made up of one or more Vacation items. Vacation items have a startDate
and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1
,
> 01/01/05, 01/05/05)
> WHERE
> ……..
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||Did you try
Insert into VacationItems
Select ....
from...
where....
Thanks,
Pradeep Kutty
"John" <John@.discussions.microsoft.com> wrote in message
news:BD4AA01F-E57C-4F9F-AD8F-70B91B8E775D@.microsoft.com...
>I have tables of Vacations and VacationItems. Each Vacation has an owner
>and
> is made up of one or more Vacation items. Vacation items have a startDate
> and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
> on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1,
> 01/01/05, 01/05/05)
> WHERE
> ...
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||First, fix the schema design. Both tables lack alternate keys. IDENTITY
should never be the only key of a table. Vacation is redundant because
it contains zero information except userid.
You can prevent overlapping dates by using a combination of constraints
and a trigger:
CREATE TABLE vacations (
userid INTEGER NOT NULL /* REFERENCES Users (userid) */,
startdate SMALLDATETIME NOT NULL ,
enddate SMALLDATETIME NOT NULL,
CONSTRAINT pk_vacation
PRIMARY KEY (userid, startdate),
CONSTRAINT ck_vacations
CHECK (startdate <= enddate)
)
GO
CREATE TRIGGER trg_vacation ON vacations
FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM vacations AS V
JOIN inserted AS I
ON I.startdate <= V.enddate
AND I.enddate >= V.startdate
AND I.startdate <> V.startdate
AND I.userid = V.userid)
BEGIN
RAISERROR('Violation of overlap constraint', 16, 1)
ROLLBACK TRAN
END
GO
David Portas
SQL Server MVP
--|||Preventing time overlaps requires user intervention => values should be
resolved in the application *before* any insert/update is attempted.
ML