sql sql-server triggers legacy default-value

Using default values in an INSTEAD OF INSERT trigger

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.

Solution

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:

Copy the Inserted virtual table's data structure to a temporary table:
```
SELECT * INTO aTempInserted FROM Inserted WHERE 1=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

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.
Insert default record into the temporary table (all fields are nullable as created from Inserted virtual table):
```
INSERT INTO aTempInserted DEFAULT VALUES
```
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
```
Drop the temporary table