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

I am getting th开发者_JAVA技巧e 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?

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

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)?

I would also be interested, because I have a stored procedure in a database that I have inherited which tries to do this. It won't let me create the sproc in SQL 2014, but the fact that it is there in the sproc indicates to me that an earlier version of SQL server must have allowed this.

Maybe in earlier versions your procedure operated on a table, which was later replaced by a view.

You should replace your "update from" syntax by standard ANSI syntax of update.