SQL Server Instead Of Insert trigger on View causes Cannot Insert Null

0
votes

We have an Instead-Of-Insert trigger on a view which copies all values from the INSERTED virtual-table to another table.

One of the fields in the list is non-nullable for the target table, and has a default value specified.

What we are experiencing, is, some application code is sending an insert command, and not specifying the non-nullable field - which (if the insert were executed against the actual table) would normally result in SQL Server inserting the column's default value. But, the trigger is explicit for all fields, so the trigger tries to insert null for that field... resulting in an error.

What I DONT want, is code like this...

INSERT INTO XXXX (col1, col2, col3) 
   SELECT 
      ISNULL(col1, 0), ISNULL(COL2, 0), ISNULL(COL3, 0) 
   FROM INSERTED

I don't want the trigger to need to know what the actual default values of each column should be (from a maintainability perspective)...

Does anyone have a better solution?

Thanks

2
sqlsql-servertriggers
Have you considered using a default value in the underlying tables? - Gordon Linoff
Thats the problem - we DO have default values for the underlying table. So when the view sends NULL for that column, it doesn't matter what the default value is on the table, the database won't allow a null, and the default wont be used instead because the View trigger query SPECIFIES an explicit null - Adam
The only way I can think of to avoid hard-coding the actual defaults would be to have an ugly query that pulls the definition from sys.default_constraints - you'd need to join to this table repeatedly, once for each column whose default you want to obtain, and you'd have to rely on the actual values being used for the default being easily convertible to the actual data type required. The complexity and brittleness of this solution makes embedding the column defaults in the trigger look (to me) like the better option. - Damien_The_Unbeliever
@Damien_The_Unbeliever agreed. - Adam

2 Answers

0
votes

when your application is sending NULL values to a not nullable column, there are not to many options. specialy when you dont want to use input validation with isnull.

we are using default values in this case. if it is possible you can alter your table:

ALTER TABLE xxxx ADD CONSTRAINT DF_col1 DEFAULT N'default' FOR col1;
0
votes

I can think of an ugly and inefficient way of doing this. The idea is to insert a default row and then update the columns one at a time, using try/catch to ignore errors.

declare @Id int;
insert int XXX DEFAULT VALUES;
set @id = @@IDENTITY;

begin try
    update XXX set col1 = val1 where id = @id;
end try
begin catch
end catch;

begin try
    update XXX set col2 = val2 where id = @id;
end try
begin catch
end catch;

. . .

If you have to do this on 100 columns, then that could be a bad idea. If you only have two or three columns causing the problems, then this might solve your problem.