BulkUpdateAll on view with instead of trigger, throws error

[Arma-Branca]

Hi,
I'm currently using BulkUpdateAll to update view with an instead of trigger.
I'm aware that this is somehow a strange usage but there's no easy workaround.

Problem

The BulkUpdateAll will try to do UPDATE t0 SET {properties} FROM to JOIN {temp table}, but due to SQLServer limitations this is not permitted for Views with instead of triggers, and throws the following error:

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

Solution

One way to solve this problem would be using a MERGE statement instead of the JOIN and maybe even enable the insertion of new elements in case of the flag InsertIfNew is enabled.

Is this something that could be done, or are there advantages in the usage of the UPDATE statement in terms of performance?

[mtanneryd]

Hi! By pure coincidence I actually worked on another view-related issue with my bulk operations package for EF Core today. I'll go through your suggested changes as soon as I can. Thanks! /Måns Måns Tånneryd Tånneryd IT AB Barrskogsvägen 19 186 53 Vallentuna +46-705140093 https://se.linkedin.com/in/manstanneryd *https://github.com/mtanneryd/ef6-bulk-operations <https://github.com/mtanneryd/ef6-bulk-operations>* Den tis 21 sep. 2021 kl 18:09 skrev Andre Almeida ***@***.***

…

: Hi, I'm currently using BulkUpdateAll to update view with an instead of trigger. I'm aware that this is somehow a strange usage but there's no easy work around. Problem The BulkUpdateAll will try to do them UPDATE t0 SET {properties} FROM to JOIN {temp table}, but due to SQLServer limitations this is not permitted for Views with instead of triggers, and throws the following error: UPDATE is not allowed because the statement updates view "" which participates in a join and has an INSTEAD OF UPDATE trigger. Solution One way to solve this problem would be using a MERGE statement instead of the JOIN and maybe even enable the insertion of new elements in case of the flag InsertIfNew is enabled. Is this something that could be done, or are there advantages in the usage of the UPDATE statement in terms of performance? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#36>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2BSRP73PDXKRTTARMG35LUDCU4DANCNFSM5EO3RM3Q> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.