Concurrency error when deleteing or updating multiple primary key fields
I ended up coding some brief tests to confirm how SQL 2016 (EXPRESS) behaves - and thought it would be appropriate to share as it might help others searching for similar information.Based on my test, it is possible to select data from the "inserted" table and use that to update the inserted data itself.The techniques outlined above describe your options pretty well. I can't imagine how a basic conflict like this between you and whoever is responsible for the software can't end up in confusion and antagonism with the users.I'd do everything I could to find some other way out of the impasse - because other people could easily see any change you make as escalating the problem.The underlying reason for is that the modifications could cause infinite recursion.
it deletes any OLD bad records, but it doesn't delete the just-inserted bad record.
I've got a table, some Table, and I'm trying to prevent people from inserting bad records.
For the purpose of this question, a bad record has a field "some Field" that is all numeric.
One option would be for the trigger to roll back the transaction.
Edit: The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger.
This is confirgured via the sp_configure stored proceedure, where you can turn recursive or nested triggers on or off.