Edit Question - KnowledgeHub

Title Body

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?

Tags (comma-separated) Cancel