Prev: CLR UDF, how to tell selectivity to the optimizer?
Next: SQL SELECT Help! (supersets)
From: Dan Holmes on 4 Mar 2010 08:13
(contrived example coming) if i sell christmas trees and hire temp workers each year and i also reward the top sellers
how can i enforce sales data within the timeframe the employee works for me? The same guy could work for me each year.
I don't want to be concerned having to figure out sales from an Emp that were outside his hiredates. the DB should do
that for me.

CREATE TABLE Employees (Fname VARCHAR(20), LName VARCHAR(20));
CREATE TABLE EmployeeHireDates( Fname VARCHAR(20), LName VARCHAR(20), HireDate DATETIME, TerminationDate DATETIME);

(insert obvious PK and FK in the abbreviated tables above).

CREATE TABLE TreeSales (Fname VARCHAR(20), LName VARCHAR(20), saleDate DATEIME, amount NUMERIC(10,4));

What i would like from the TreeSales table is DRI to the EmployeeHireDates tables not just on the employee name but also
ensure that the SaleDate is within the employee's hiredates. Something like

ALTER TABLE TreeSales
ADD CONSTRAINT FK_TreeSales_HireDates
FOREIGN KEY hd(Fname , LName , saleDate )
REFERENCES EmployeeHireDates ed(Fname , LName , saleDate )
WHERE hd.Fname = ed.fname AND hd.lname = ed.lname
AND hd.SaleDate BETWEEN ed.Hiredate AND ed.TerminationDate


Indexes can now have WHERE clauses, perhaps FOREIGN KEYs should too. I can see where theta operators would be valuable
as well.

Has anyone else ever needed this? how do you do this now? A trigger?

danny
From: --CELKO-- on 4 Mar 2010 08:43
>> Has anyone else ever needed this?  how do you do this now? A trigger? <<

Yes; in Full Standard SQL we use a CHECK() that references another
table, or the CREATE ASSERTION statement.

In SQL Server, you are stuck with TRIGGERs.
From: Gert-Jan Strik on 4 Mar 2010 17:33
Dan Holmes wrote:
>
> (contrived example coming) if i sell christmas trees and hire temp workers each year and i also reward the top sellers
> how can i enforce sales data within the timeframe the employee works for me? The same guy could work for me each year.
> I don't want to be concerned having to figure out sales from an Emp that were outside his hiredates. the DB should do
> that for me.
>
> CREATE TABLE Employees (Fname VARCHAR(20), LName VARCHAR(20));
> CREATE TABLE EmployeeHireDates( Fname VARCHAR(20), LName VARCHAR(20), HireDate DATETIME, TerminationDate DATETIME);
>
> (insert obvious PK and FK in the abbreviated tables above).
>
> CREATE TABLE TreeSales (Fname VARCHAR(20), LName VARCHAR(20), saleDate DATEIME, amount NUMERIC(10,4));
>
> What i would like from the TreeSales table is DRI to the EmployeeHireDates tables not just on the employee name but also
> ensure that the SaleDate is within the employee's hiredates. Something like
>
> ALTER TABLE TreeSales
> ADD CONSTRAINT FK_TreeSales_HireDates
> FOREIGN KEY hd(Fname , LName , saleDate )
> REFERENCES EmployeeHireDates ed(Fname , LName , saleDate )
> WHERE hd.Fname = ed.fname AND hd.lname = ed.lname
> AND hd.SaleDate BETWEEN ed.Hiredate AND ed.TerminationDate
>
> Indexes can now have WHERE clauses, perhaps FOREIGN KEYs should too. I can see where theta operators would be valuable
> as well.
>
> Has anyone else ever needed this? how do you do this now? A trigger?
>
> danny

Joe gave you the right answer.

I don't see such a feature coming anytime soon. It has many facets and
implications (which you will all have to face as well if you try to
implement this with triggers). Such as a NULL TerminationDate (which
would allow any SaleDate even if it before the HireDate). And the cases
in which you change HireDate and/or TerminationDate. And the case where
you have registered two overlapping hire periods for the same employee,
etc. etc.

--
Gert-Jan
 | 
Pages: 1
Prev: CLR UDF, how to tell selectivity to the optimizer?
Next: SQL SELECT Help! (supersets)