SQL Server Performance Forum – Threads Archive

CONDITION CHECK

HOW DO I IMPLEMENT THIS CONDITION @ the db level, where STATUS , STATUSDate are columns of a table test. that if STATUS = 1 the STATUSDate cannot be NULL. SHOULD I USE TRIGGER OR CONSTRAINTS OR RULE AND HOW. Thanks

---

Usually row level constraints are great for making sure inserted/updated data is acceptable for that row independently. i.e. age is a positive number. When you want to cross reference other rows in making decisions especially, that is where it is time to use a trigger. What you want to do in the event of a conflict is up to you. You may want to roll back the transaction that made the change or you may want to change STATUSDate from null to something else. Start with triggers for the insert and update events and work from there once you decide what the correction should be. John

---

If I understand you correctly, it might look something like this:
CREATE TABLE #test
(
status INT
, StatusDate DATETIME
) ALTER TABLE #test
ADD CONSTRAINT chk_status CHECK((status = 1 AND StatusDate IS NOT NULL) OR (status != 1)) INSERT INTO #test
SELECT 1, GETDATE()
UNION ALL
SELECT 2, NULL SELECT *
FROM #test INSERT INTO #test
SELECT 1, NULL DROP TABLE #test
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

]]>