CODEWITHSUNDEEP
sqlsql-servert-sql
azim
azim

Reputation: 595

Set value of the column to default for NULL values

I have a table with a column which has default value to 0. However when null is inserted to this table, I want it to set it to default.

As the insert query is generic and used by other databases too, I cannot make any changes to the insert statement.

Can I have constraints or case statement on create table, so that default 0 value is inserted whenever null is passed.

Upvotes: 0

Views: 144

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

If you can not change an insert statement you have no way other then creating an instead of insert trigger:

CREATE TRIGGER trTableName 
ON SchemaName.TableName
INSTEAD OF INSERT
AS
BEGIN

    INSERT INTO TableName (ColumnA, ColumnB, ...)
    SELECT ISNULL(ColumnA, 0), ISNULL(ColumnB, 0), ...
    FROM INSERTED

END

Upvotes: 3

LittleMissSQL
LittleMissSQL

Reputation: 11

Inserting null is the same as inserting a specific value, and so the default value is bypassed.
To use the default setting the insert statement shouldn't insert anything to this column.

If you can't use Coalesce (value, 0) in the select bit of the into statement, try using it in your select queries to disguise the result.

Upvotes: 1

Kaf
Kaf

Reputation: 33809

You can do an update using a trigger on insert.

CREATE TRIGGER [dbo].[YourTriggerName]
   ON  [dbo].[YourTable]
   AFTER  INSERT 
AS 
BEGIN
      UPDATE t SET YourCol = 0
      FROM YourTable t
           JOIN Inserted i ON t.Id = i.Id
      WHERE i.YourCol IS NULL 
END

Upvotes: 3

Related Questions