Using default values in an INSTEAD OF INSERT trigger

Question

We are performing a database migration to SQL Server, and to support a legacy app we have defined views on the SQL Server table which present data as the legacy app expects.

However, we're now having trouble with INSTEAD OF INSERT triggers defined on those views, when the fields may have default values.

I'll try to give an example.

A table in the database has 3 fields, a, b, and c. c is brand new, the legacy app doesn't know about it, so we also have a view with 2 fields, a and b.

When the legacy app tries to insert a value into its view, we use an INSTEAD OF INSERT trigger to lookup the value that should go in field c, something like this:

INSERT INTO realTable(a, b, c) SELECT Inserted.a, Inserted.b, Calculated.C FROM...

(The details of the lookup aren't relevant.)

This trigger works well, unless field b has a default value. This is because if the query

INSERT INTO legacyView(a) VALUES (123)

is executed, then in the trigger, Inserted.b is NULL, not b's default value. Now I have a problem, because I can't tell the difference the above query, which would put the default value into b, and this:

INSERT INTO legacyView(a,b) VALUES (123, NULL)

Even if b was non-NULLABLE, I don't know how to write the INSERT query in the trigger such that if a value was provided for b, it's used in the trigger, but if not the default is used instead.

EDIT: added that I'd rather not duplicate the default values in the trigger. The default values are already in the database schema, I would hope that I could just use them directly.

Answer 1

Try

INSERT INTO legacyView(a, b) VALUES (123, DEFAULT)

More info is in the CREATE DEFAULT section of the MSDN T-SQL documentation.

Answer 2

Some ideas:

• If the legacy application is specifying column lists for INSERTs, and naming columns rather than using SELECT *, then can't you just bind a default to column c and let the application use your original (modified) table?

• If there was any way that you could make the legacy app use a different view or table for its INSERTs than for SELECT or DELETE, you could put the required defaults on that table and use a regular after-trigger to move the new columns over to the real table.

• How about leaving the original table alone and adding your additional columns in a separate table which has a 1-1 relationship with the original? Then create a view that combines these two tables and put appropriate instead-of trigger(s) on this new view to handle all data operations split across the two tables. I realize this has performance implications, but it might be the only way around the problem. This would be an ideal case for a materialized view, which would slow down updates but make the result perform exactly like a table for reads. (Materialized views lend themselves best to inner joins and require no aggregation. They also put schema locks on the source tables.)

• I've run into a similar problem where I couldn't tell the difference between intentionally NULL values and skipped columns in an instead-of UPDATE trigger on a view. I eventually made an instead-of INSERT trigger on the view to convert inserts to updates (if the key already existed it was an update, otherwise it was an insert). Though this won't help you directly, it might spur some ideas for you or others.

Answer 3

Paul: I've solved this one; eventually. Bit of a dirty solution and might not be to everyone's taste but I'm quite new to SQL Server and such like:

In the Instead_of_INSERT trigger:

1. Copy the Inserted virtual table's data structure to a temporary table:

   SELECT * INTO aTempInserted FROM Inserted WHERE 1=2
   

2. Create a view to determine the default constraints for the view's underlying table (from system tables) and use them to build statements which will duplicate the constraints in the temporary table:

   SELECT  'ALTER TABLE dbo.aTempInserted  
   ADD CONSTRAINT ' + dc.name + 'Temp' + ' DEFAULT(' + dc.definition + ') 
   FOR ' + c.name AS Cmd, OBJECT_NAME(c.object_id) AS Name
   FROM sys.default_constraints AS dc INNER JOIN
   sys.columns AS c ON dc.parent_object_id = c.object_id 
   AND dc.parent_column_id = c.column_id
   

3. Use a cursor to iterate through the set retrieved and execute each statement. This leaves you with a temporary table with the same defaults as the table to be inserted into.

4. Insert default record into the temporary table (all fields are nullable as created from Inserted virtual table):

   INSERT INTO aTempInserted DEFAULT VALUES
   

5. Copy the records from the Inserted virtual table into the view's underlying table (where they would have been inserted originally, had the trigger not prevented this), joining the temporary table to supply default values. This requires use of the COALESCE function so that only unsupplied values are defaulted:

   INSERT INTO realTable([a], [b], 
   SELECT COALESCE(I.[a], T.[a]), COALESCE(I.[a], T.[b])
   FROM Inserted AS I, aTempInserted AS T
   

6. Drop the temporary table