SQL Server Performance

CONDITION CHECK

Discussion in 'SQL Server 2005 General Developer Questions' started by johnlv, May 23, 2006.

  1. johnlv New Member

    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
  2. spacemonkey New Member

    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
  3. FrankKalis Moderator

    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)

Share This Page