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.
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'
A view with an INSTEAD OF UPDATE trigger cannot be a target of an
UPDATE with a FROM clause.
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)?