Post reply

Use trigger or not?

  • May 30, 2006 at 12:58 am

    #112426

    I want to prevent a column in a table from being updated once it has a value. The column has a bit data type. The default value is NULL. If the column has a value of either null or 0 it should be updated to 1. If it has a value of 1 it should not be updated. What is the best way to do this?

    Thanks in advance

     

     

     

  • May 30, 2006 at 2:10 am

    #640605

    Use a trigger, that is the only solution i can think of!!

    Brij

  • May 30, 2006 at 3:36 am

    #640619

    The only way to guarantee it would be to have a trigger.  You could also have as one of your rules that all table access is via stored proc only and the stored proc can enforce this rule (but that's a debate which I don't think we want to start on this thread! )

    So yes, a trigger is the way.  It is up to you if you just ignore the update (ie, you'll need to restore the original value in the trigger if the row is updated) or, if you detect the value has changed, issue a rollback for the transaction.

  • May 30, 2006 at 7:10 am

    #640666

    Unless I missed something here

    quoteThe column has a bit data type

    Therefore can only be null,0 ro 1

    quoteIf the column has a value of either null or 0 it should be updated to 1

    The result of the logic is that the column will end up set to 1 in any case

    quoteIf it has a value of 1 it should not be updated

    Why not, the result will be the same unless you have another trigger looking for changes to the column in which case a trigger will not work unless it is an INSTEAD of trigger

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 30, 2006 at 5:06 pm

    #640809

    Yes, a trigger  I think - but a different db design would be better. It looks like this field is meant to track some sort of progress. It might be better (ideally) to store a record of the steps in this progress, either as rows in another table, or (if the operational procedures you are tracking are very firmly fixed) as fields, e.g. booked date and user, confirmation date and user, completion date and user. Very often a status flag like this is actually denormalised data which could be constructed from elsewhere in the schema, so a calculated column might even be a possibility.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • May 31, 2006 at 2:06 am

    #640842

    Thanks for the excellent suggestions.

    Stax68, I think that your suggestion is superb and I am already looking into it.

    Thanks

  • May 31, 2006 at 7:34 am

    #640917

    Gald to be of help. The need for complex or difficult constraints (e.g. the kind that need triggers) can often (not always!) be avoided by improved normalisation of your database design.

    And normalisation brings many other benefits (or better: non-normalised schemas bring many other problems) which I won't go into here...

    Let us know how you get on and what you decide.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply