View + INSTEAD OF UPDATE + UPDATE FROM

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 11:40:30 +0200

I guess I should have read the small print in Books Online first.....

After noticing the huge performance difference between ANSI standard
UPDATE syntax and proprietary UPDATE FROM syntax, I decided to use the
latter for a time critical system.

After writing 323 procedures, totaling 7169 lines of code, I go to the
test server, import all my code and start a test.

Server: Msg 4422, Level 16, State 1, Procedure XXXXX, Line 7
View 'dbo.YYYYY' has an INSTEAD OF UPDATE trigger and cannot be a target
of an UPDATE FROM statement.

Drat! Time to revisit 7169 lines of code, I'm afraid......

Unless somebody here knows of an easy workaround?

Oh - I'd also be interested to know WHY the UPDATE FROM syntax can't be
used for views with an instead of update trigger. If I read BOL correctly,
it can be used on a view with an AFTER trigger, it can be used on a table
with an INSTEAD OF trigger - just not on a view with in instead of
trigger. Many of SQL Server's limitations make sense when you consider it
from an architectural point of view. I fail to see the sense for this
limitation. Anyone care to enlighten me?

TIA!

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Using finite signals in Simulink
    ... This is a command-line type syntax that is not allowed; ... eml.extrinsic since it's not supported natively in Embedded MATLAB. ... the cyclic repetition is not an option since the signal ... when the trigger was left on and then the simulation was ...
    (comp.soft-sys.matlab)
  • Re: Can this be done with a trigger?
    ... CREATE OR REPLACE TRIGGER customer_config_trg ... I can't check for syntax on this one since ... (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED, ... -- insert into the history table ...
    (comp.databases.oracle.misc)
  • Re: View + INSTEAD OF UPDATE + UPDATE FROM
    ... I don't remember the logic for this limitation. ... workarounds, but they aren't pretty. ... SQL Server allows the UPDATE FROM syntax. ... > used for views with an instead of update trigger. ...
    (microsoft.public.sqlserver.programming)
  • Re: interbase/firebird cascade
    ... Ich kann die Syntax nie aus dem Kopf. ... Alternativ lässt Du den ... Trigger einfach weg. ...
    (de.comp.lang.delphi.datenbanken)
  • Trigger on a Qfile ... WHAT??
    ... So dealing with the limitations in D3, as you have to do in any ... environment, we have just about everything working, kludged etc EXCEPT ... out biggest problem is the naming convention. ... way to attach a trigger to that? ...
    (comp.databases.pick)