Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005

    Unanswered: Quick Question... How to use Default Values (after allowing NULL)


    I have a BIT column which accepts NULL values.

    What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

    Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

    update YourTable
    set YourValue = 0
    from YourTable
    inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
    where YourValue is null

    But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Apr 2005
    Thx for the quick response.

    I'll give it a shot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts