date check before insert
-
Hi. I have a table that is used to schedule courses for colleges that have several branches around the country.
this table looks something like this
schedule:(schedID, classID, branchID, studentID, trainerID, start, end_date)
start is the start date of the course and end_date is the ending date. Now what I need is a method that will check the start and end dates when I insert a new record into this table so that a student or trainer cannot be entered into the table on a schedule that has a date that falls within the start and end dates of other records for that student or trainer in the schedule table.
So for example if student a is booked on a course that finished on starts on the 05/05/2005 and ends on the 05/10/2005, then that student can only be entered for another schedule if the dates dont conflict. So I guess its like some kind of trigger that will check the studentID and check the start and end dates he is scheduled for, and then will only allow that student to be inserted when the dates on overlap.
Here is the ddl of the table schedule:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule_classID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[schedule_classID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule_courseID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[schedule_courseID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule_branchID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[schedule_branchID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule_studentID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[schedule_studentID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule_trainerID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[schedule_trainerID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[schedule]
GO
CREATE TABLE [dbo].[schedule] (
[schedID] [int] IDENTITY (1, 1) NOT NULL ,
[classID] [int] NOT NULL ,
[courseID] [int] NOT NULL ,
[branchID] [int] NOT NULL ,
[studentID] [int] NOT NULL ,
[trainerID] [int] NOT NULL ,
[start] [smalldatetime] NOT NULL ,
[end_date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[schedule] WITH NOCHECK ADD
CONSTRAINT [PK_schedule] PRIMARY KEY CLUSTERED
(
[schedID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[schedule] WITH NOCHECK ADD
CHECK ([end_date] > [start]),
CONSTRAINT [CK__schedule__start__3B40CD36] CHECK ([start] >= getdate()),
CHECK ([start] > getdate() or [start] = getdate())
GO
ALTER TABLE [dbo].[schedule] ADD
CONSTRAINT [FK_schedule_branches] FOREIGN KEY
(
[branchID]
 
REFERENCES [dbo].[branches] ([branchID]
 
,CONSTRAINT [FK_schedule_class] FOREIGN KEY
(
[classID]
 
REFERENCES [dbo].[class] ([classID]
 
,CONSTRAINT [FK_schedule_course] FOREIGN KEY
(
[courseID]
 
REFERENCES [dbo].[course] ([courseID]
 
,CONSTRAINT [FK_schedule_Students] FOREIGN KEY
(
[studentID]
 
REFERENCES [dbo].[Students] ([studentID]
 
,CONSTRAINT [FK_schedule_trainers] FOREIGN KEY
(
[trainerID]
 
REFERENCES [dbo].[trainers] ([trainerID]
 
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER schedule_classID on dbo.schedule
AFTER INSERT
AS
IF NOT EXISTS(SELECT * FROM class c JOIN Inserted I ON
c.classID=I.classID)
BEGIN
RAISERROR('The classID could not be found', 16, 1)
ROLLBACK TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER schedule_courseID on dbo.schedule -- check to see courseID inserted into schedule is valid
AFTER INSERT
AS
IF NOT EXISTS(SELECT * FROM course cr JOIN Inserted I ON
cr.courseID=I.courseID)
BEGIN
RAISERROR('The courseID cound not be found', 16, 1)
ROLLBACK TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER schedule_branchID on dbo.schedule -- check to see branchID inserted into schedule is valid
AFTER INSERT
AS
IF NOT EXISTS(SELECT * FROM branches b JOIN Inserted I on
b.branchID=I.branchID)
BEGIN
RAISERROR('The branchId could not be found', 16, 1)
ROLLBACK TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER schedule_studentID on dbo.schedule
AFTER INSERT
AS
IF NOT EXISTS(SELECT * FROM students s JOIN Inserted I ON
s.studentID = I.studentID)
BEGIN
RAISERROR('The student does not exist', 16, 1)
ROLLBACK TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER schedule_trainerID on dbo.schedule
AFTER INSERT
AS
IF NOT EXISTS(SELECT * FROM trainers t JOIN Inserted I ON
t.trainerID=I.trainerID)
BEGIN
RAISERROR('The trainer you have tried to insert dosnt exist', 16, 1)
ROLLBACK TRANSACTION
END
----
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
You could use a trigger but rolling back transactions on atrigger can cause problems down the line you should rather do it up front!
Are you allowing direct access to the table ?
Can you control access through stored procedures instead ?
* Noel -
I could use a stored procedure, but I dont know how i would code it, Im still a beginner at sql, so any help would be greatly appreciated
-
as long as all your DML (data modifications) go through stored procedures it is safer to to write something like this:
create proc ins_schedule (@schedID int output, @classID int, @branchID int, @studentID int, @trainerID int, @start datetime, @end_date datetime)
as
begin
declare @err int, @cnt int
--Single statement No Eplicit transaction needed
insert into sechedules( classID, branchID, studentID, trainerID, start, end_date)
select classID, branchID, studentID, trainerID, start, end_date
from
(select @classID classID, @branchID branchID, @studentID studentID, @trainerID trainerID, @start start, @end_date end_date) s
where
not exists (select * from sechedules d
where (d.studentID = s.studentID OR d.trainerID = s.trainerID)
and
(d.start >= @start and d.end_date <= @end_date) )
select @err = @@error, @cnt = @@rowcount, @ScheduleID =Scope_identity()
if @err <> 0
Return -1 -- Failure
if @cnt <> 1
Return -2 -- Already Exists
Return 0 -- Success
end
at this point you don't need triggers anymore
* Noel -
Thanks, I'll apply that and see if it works.
thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply