Posted by Hugo Kornelis on 08/06/05 17:16

On Sat, 6 Aug 2005 11:55:40 +0000 (UTC), Erland Sommarskog wrote:

>So are there cases when triggers beats constraints? Yes. Say that you
>have an CustomerCategories table that has an IsActive flag. An active
>customer must belong to a customer category which also is active. This
>can be implemented with a constraint, if you use a UDF to check the
>status of the customer category.

Hi Erland,

Am I missing something from your description? Unless I do, the best wat
to do this is to add a (seemingly redundant) UNIQUE constraint, then use
a standard FOREIGN KEY constraint:

CREATE TABLE Categories
(CategoryID int NOT NULL,
IsActive char(1) DEFAULT 'Y',
-- other columns,
PRIMARY KEY (CategoryID),
CHECK (IsActive = 'Y' OR IsActive IS NULL),
UNIQUE (CategoryID, IsActive),
)

CREATE TABLE Customers
(CustomerID int NOT NULL,
IsActive char(1) DEFAULT 'Y',
CategoryID int NOT NULL,
-- other columns,
PRIMARY KEY (CustomerID),
CHECK (IsActive = 'Y' OR IsActive IS NULL),
FOREIGN KEY (CategoryID, IsActive)
REFERENCES Categories (CategoryID, IsActive),
)

Okay, I agree that it's somewhat hackish - having IsActive either 'Y' or
NULL is far from intuitive. In real life, I'd probably go for the
trigger as well :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]