Help needed with Instead of Update trigger on a View

I have an application where we are replacing a subsystem including portions
of the database. In order to minimize the code impact on the existing
application, we have decided to create a few "compatibility views" - i.e.
database Views that produce the same result and with the same names as the
old tables. Also in order to allow existing code to continue to function,
we are implementing INSTEAD OF triggers on the views. Even though all our
database accesses are encapsulated in stored procs, we have around 1500 of
them, and one of the tables we needed to reengineer this way is the "main"
table for the entire app. To make sure this isn't trivial, we have a new
"master" entity table with an Identity column that is referenced by the
reengineered "main" table.
At this point, we are only aware of performance impacts - everything appears
to work OK:
1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
statement) is part of an index, we loose the use of the index as a result of
having to use NULLIF() or COALESCE() on those columns in the view. For the
same reason, we can't index those view(s).
2. (This is where the question comes in:) The INSTEAD OF UPDATE trigger
appears to require a large number of separate UPDATE statements against the
base tables, or building a dynamic SQL statement. We are looking for
guidance...
Now to my question:
In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
table. If I understand correctly, since the triggering update statement may
only update one column, I cannot use an UPDATE statement against the base
table that updates all the columns with the values from the 'updated'
pseudo-table. Instead, I will have to check if each column is updated, and
if so, either update it separately or build a dynamic SQL UPDATE statement
including those columns that have been updated.
What is the recommended approach to this?
TIA,
Tore.On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
(snip)
>1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
>statement) is part of an index, we loose the use of the index as a result o
f
>having to use NULLIF() or COALESCE() on those columns in the view.
Hi Tore,
I don't think I understand what you're saying here. Where are you using
NULLIF() or COALESCE() and why? Coould you post a simplified sample of
your code?

> For the
>same reason, we can't index those view(s).
And neither should you. If you index the views, you'll create a complete
copy of your data. I don't think that yoou should do that in your
scenario.
(snip)
>In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
>table. If I understand correctly, since the triggering update statement ma
y
>only update one column, I cannot use an UPDATE statement against the base
>table that updates all the columns with the values from the 'updated'
>pseudo-table.
You undersatnd incorrectly. There is no 'updated' pseudo-table. The
'deleted' and 'inserted' pseudo-tables contain the complete before and
after image of the updated rows, including all columns that are not
affected by the update.
If your compatibility view translates to one new table, just perform the
modification in a single UPDATE statement. If your compatibility view
translates to more than one new table, use
IF UPDATE(col1) OR UPDATE(col2) ....
to find out which table(s) need updating, then use a single UPDATE
statement for all rows in each of the tables.
Sure, you'll be setting columns to the same value they already had. The
added cost of that is much less than the cost of finding out which
columns to update and executing up to 90 (!) consecutive UPDATE
statements against the same set of rows.

> Instead, I will have to check if each column is updated, and
>if so, either update it separately or build a dynamic SQL UPDATE statement
>including those columns that have been updated.
Using the dynamic SQL is even a worse option - it forces you to give
every user update permissions to the table. (And it will be slow because
of the extra recompiles).
www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP|||Thanks Hugo,
I'll be looking at this tomorrow.
Tore.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p75762lfnlmg789b20fv9jk1jfjrhs2it0@.
4ax.com...
> On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
> (snip)
of
> Hi Tore,
> I don't think I understand what you're saying here. Where are you using
> NULLIF() or COALESCE() and why? Coould you post a simplified sample of
> your code?
>
> And neither should you. If you index the views, you'll create a complete
> copy of your data. I don't think that yoou should do that in your
> scenario.
> (snip)
may
> You undersatnd incorrectly. There is no 'updated' pseudo-table. The
> 'deleted' and 'inserted' pseudo-tables contain the complete before and
> after image of the updated rows, including all columns that are not
> affected by the update.
> If your compatibility view translates to one new table, just perform the
> modification in a single UPDATE statement. If your compatibility view
> translates to more than one new table, use
> IF UPDATE(col1) OR UPDATE(col2) ....
> to find out which table(s) need updating, then use a single UPDATE
> statement for all rows in each of the tables.
> Sure, you'll be setting columns to the same value they already had. The
> added cost of that is much less than the cost of finding out which
> columns to update and executing up to 90 (!) consecutive UPDATE
> statements against the same set of rows.
>
statement
> Using the dynamic SQL is even a worse option - it forces you to give
> every user update permissions to the table. (And it will be slow because
> of the extra recompiles).
> www.sommarskog.se/dynamic_sql.html
> --
> Hugo Kornelis, SQL Server MVP