5

Sometimes for refactoring, I use a View to abstract the changes and use INSTEAD OF Triggers to mimic the previous functionality.

I've run into error 414 (or 415) in the past

UPDATE is not allowed because the statement updates view "%.*ls" which participates in a join and has an INSTEAD OF UPDATE trigger.

I can rewrite the UPDATE FROM to a MERGE statement and that works. But why?

I found these references, but none of them answers why.

https://stackoverflow.com/questions/3085036/update-is-not-allowed-because-the-statement-updates-view-table-name-which-part

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/87c173b0-8f87-4aa3-b861-d40f23803a43/views-and-instead-of-update-trigger-limitation-why?forum=transactsql

https://sqlserverfast.com/blog/hugo/2008/03/lets-deprecate-update-from/

0

1 Answer 1

6

My understanding is this is disallowed because UPDATE...FROM has some quirky behaviours that are maintained due to backward compatibility. Making these work the same when the view has instead-of triggers would be difficult, perhaps impossible.

MERGE has several weaknesses, but it does have well-defined and sane update semantics.

The primary example of this is MERGE preventing ambiguous updates—where a target row is logically changed more than once by the SQL specification. If nothing in the schema absolutely guarantees a target row cannot be modified more than once, a MERGE plan introduces operators to check this condition at runtime, and raise an error if it encountered.

This makes implementing joined-table MERGE on a target with instead-of triggers easier/at all possible.

The UPDATE...FROM syntax also has curious binding behaviours (again maintained to avoid breaking old code) when the target table is referenced more than once, sometimes by alias and sometimes not.

Not directly related to your question, but there is an example of an edge case with CTEs described in the documentation:

When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results.

All that said, just because it works with MERGE doesn't mean there aren't bugs lurking. For a semi-related example see MERGE into a view with INSTEAD OF triggers.

Pushing hard at the edges of combinations of complex features is a great way to find edge-case bugs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.