11th June 23:37
ADO.NET with Instead-Of-Insert Triggers, sets NULLs
This has confused me and I have not been able to figure out what is
happening. I have a simple application that is developed in Visual
Studio .NET and I used the wizard template to create a SDI App with
ODBC support (no file support). The application allows a user to
insert records to a table in a Microsoft SQL Server database through a
Now the catch is the data is inserted through a View. I'm able to get
the insert to work when using an Instead-Of-Insert trigger and
defining the View with the VIEW_METADATA attribute.
The following article describes this issue:
I have been able to determine that the trigger fires and data is
handled and processed. The problem is the View has a column that is
"massaged" and that column is always set to NULL. It shouldn't be
NULL; it should have a value based on the user-input. The
instead-of-trigger is supposed to take the value passed by the
application and use the value returned by a function to properly store
it into the base table. My assumption is that there is a problem with
the RFX (Record Field Exchange) process. Stepping through the VC++
code, the data is set properly in the DoFieldExchange method.
I expected the value to be passed into the [inserted] table used by
the trigger. But the data in the [inserted] table is NULL. I had
placed a debug statement placed inside the trigger to print out the
the values that were stored in the [inserted] table that the trigger
uses. The value that is received for the column that needs to be
"massaged" is always NULL, even though I watched it get set properly
on the client application side. The values for the other columns in
the view are fine. Only the columns that must go through a function
are passed a NULL.
Can anyone shed some light on this issue?