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

Question

I am getting the following error while executing the following query in an Stored Procedure. Could anyone help in finding the fault?

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

UPDATE si
SET 
    name = mc.name,
    sup_item_cat_id = mc.res_sup_item_cat_id,
    xf_value = mc.xf_value,
    ava_start_date = mc.ava_start_date,
    ava_end_date = mc.ava_end_date,
    status_code = mc.status_code,
    last_mod_us_id = CASE WHEN mc.last_mod_us_id = 42 THEN @posting_us_id 
                     ELSE mc.last_mod_us_id END,
    last_mod_tsp = CURRENT_tsp
FROM sup_item AS si
    JOIN merch_cat_imp_sup_item AS mc
        ON mc.sup_id = si.sup_id
        AND mc.res_sup_item_id = si.sup_item_id
        AND mc.cat_imp_event_id = @cat_imp_event_id
        AND mc.accept_flag = 'y'
WHERE si.shi_flag = 'n'

I found the reference: http://msdn.microsoft.com/en-us/library/ms177523.aspx

A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.

So, I have to rewrite the UPDATE statement (it still can be in a procedure) to NOT use sup_item (which is a view), but keep the underlying table(s) as needed.

Could someone please rewrite it, if anyone knows what to do?

Answer 1

You can use MERGE to achieve this. Try:

MERGE INTO  sup_item  si
USING merch_cat_imp_sup_item AS mc
        ON mc.sup_id = si.sup_id
        AND mc.res_sup_item_id = si.sup_item_id
        AND mc.cat_imp_event_id = @cat_imp_event_id
        AND mc.accept_flag = 'y'
        AND si.shi_flag = 'n'
WHEN MATCHED
THEN UPDATE
SET 
    name = mc.name,
    sup_item_cat_id = mc.res_sup_item_cat_id,
    xf_value = mc.xf_value,
    ava_start_date = mc.ava_start_date,
    ava_end_date = mc.ava_end_date,
    status_code = mc.status_code,
    last_mod_us_id = CASE WHEN mc.last_mod_us_id = 42 THEN @posting_us_id 
                     ELSE mc.last_mod_us_id END,
    last_mod_tsp = CURRENT_tsp

Answer 2

The issue is not within your query. As per comments on your question, the entity you are updating [sup_item], isn't actually a table, it's a view. That view has an INSTEAD OF UPDATE trigger on it.

Are you able to post the SQL for the View and for the Trigger(s)?