Preventing inapropriate rows from being inserted into SQL Server database

I have a table for tent rents with the following columns:

RentID, DateStarted, DateEnded, CustomerID, etc.  

DateStarted is always entered at the beginning of the rental period, DateEnded is initally NULL and only gets filled when the rental period expires. CustomerID is the ID of the customer who rented the tent.

What I need to do is prevent users from inserting new rows for any given customer (denoted by CustomerID) if that customer has any rents with DateEnded=NULL.

I don't know how to do this. I was thinking of a Check constraint, but they don't allow me to see other rows. I was thinking of Unique constraints, but don't know if they could be used in this case. I was thinking of On-Insert trigger, but I'm not sure if I can cancel the in开发者_StackOverflow中文版sert if the condition is not met.

Thanks for any help.

---

If you are on SQL Server 2008.

CREATE UNIQUE INDEX ix ON Rents(CustomerID) WHERE DateEnded IS NULL

Earlier Versions you can use an indexed view (For this option various SET options need to be configured correctly for connections that update the base table but in SQL Server 2005 they probably will be already.)

CREATE VIEW dbo.ConstrainRents
WITH SCHEMABINDING
AS
SELECT CustomerID
FROM dbo.Rents 
WHERE DateEnded IS NULL

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ConstrainRents(CustomerID)

---

What you describe sounds a bit more on the side of your application logic. I would think that when you think about your database, and what "rules" you want to enforce, think of what would make the data record wrong. From what you describe, a record with a null DateEnded is a valid record.

From my experience triggers are wonderful if you really need them, but are very dangerous, and very quickly increase the complexity of a problem exponentially.

I would personally avoid a trigger if I can, and do that logic on my application side.

Just my 2 cents

---

I would suggest you take a look at MS's instead of triggers. Documentation here... http://msdn.microsoft.com/en-us/library/ms175521.aspx

You could also use a rollback transaction in a traditional insert trigger.