SmartestVEGA SmartestVEGA - 1 year ago 96
SQL Question

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 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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download