[posted and mailed, please reply in news]
Dan Williams (dt********@hotmail.com) writes:
To me it obviously appears better to use my first suggestion. But, how
do i go about ensuring that each employee has at least one entry in the
EmployeeBranch table, and that each employee can only have one
occurrence of each individual branch (ie. there's no duplication of
EmployeeBranch data)?
Since SQL Server does not have deferred constraints or triggers, this
is not terribly easy to implement.
Here is a fairly kludgy way of doing it:
CREATE TABLE employees(empid int NOT NULL,
firstbranch smallint NOT NULL,
.....
CONSTRAINT pk_emp PRIMARY (empid))
go
CREATE TABLE jobassignments (empid int NOT NULL,
branchid smallint NOT NULL,
CONSTRAINT pk_jobasg (empid, branchid))
go
Then you have a trigger on employees that inserts a row into
jobassignments, using the value in firstbranch. From that point and on,
firstbranch would be a dead value.
Another trigger on jobassignments would disallow removing the last branch
for an employee. But since that trigger would prevent you to delete an
employee at all, you would need a way to override the check. One way is
to have an INSTEAD OF DELETE trigger on employees that creates a temp
table #delete$pending and then performs the deletes in jobassignments
and employees. The check in jobassignments actually looks like this:
IF object_id('tempdb..#delete$pending') IS NULL
BEGIN
-- Check that not all branches for an employee is deleted.
I'm not sure that I would recommend anyone actually do this.
The more normal way to do this in SQL Server is to expose procedures
that performs the tasks and necessary integrity checking. Then you
disallow direct access to the table, and hope that people who have admin
access from Query Analyzer know what they are doing.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp