Enforcing limits on table values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Enforcing limits on table values

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-08 : 11:39:45

Say I'm working on a few tables to track employee scheduling. There is a table charting an employees availability, and also one tracking time scheduled.

Create Table Availability
(
EmployeeID int
, Date smallDateTime
, Hours int
)

Create Table Schedule
(
EmployeeID int
, Date smallDateTime
, Hours int
)

SELECT * FROM Availability
EmpID Date Hours
22 2007-01-01 4
22 2007-01-02 6

My Question is this: When I insert data into the schedule, what is the best way to ensure that an employee, on a given day, is not scheduled for more time then they are available?

So, this is legal, because EmployeeID 22 has 4 hours available on jan-01:
INSERT INTO Schedule (EmployeeID , Date, Hours) Values (22, 2007-01-01, 3)

But this isn't, because 7 hours is more then available.:
INSERT INTO Schedule (EmployeeID , Date, Hours) Values (22, 2007-01-01, 7)

Likewise, I wouldn't want to have an employee available for more then 24 hours on a day (or probably 12, labor laws n all

).But by itself, an int doesn't prevent this. Whats the workaround?

---------------------------------------------------------
SSRS Kills Kittens.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-08 : 12:00:48

(beware short cuts when typing in your code....your field names don't match up with your schedule + availability tables)

how about something like....

insert into schedule (employeeid, date, hours)
select @empidvar, @datevar, @hoursvar from employee a
left join availability b on a.empid = b.employeeid and b.date = @datevar and b.hours > @hoursvar
where b.employeeid is not null

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-08 : 12:22:57

Field names fixed. :)

Thanks for the reply, but I'm assuming I don't have control over what other users query. They could still do:
INSERT INTO Schedule (EmployeeID , Date, Hours) Values (22, 2007-01-01, 7)

and schedule for more then available, right? I guess what I'm looking for is something like a foreign key constraint, but instead of checking an identity in another table it checks if the value is less/greater then.

---------------------------------------------------------
SSRS Kills Kittens.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-09 : 06:32:16

you could put an "INSTEAD OF INSERT/UPDATE" trigger on the schedule table....which does this cross-table validation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources