Because SQL Server will roll back only the unsuccessful statements bot not the ones that succeeded, in a multi-statements transaction. This is...

Forgot to say thanks for the solution, I'm itching to see it implemented.

I still have my eyebrows up since I've learned what the heck is the dev doing... But so be it, it falls under special requests section not to be debated...

Two things on that:

1. You code example relies on running at the SERIALIZABLE isolation level.

2. In general, MERGE is not bullet-proof. See UPSERT race condition with MERGE by MVP Dan...

The problem should be easily solved with a MERGE statement inside your trigger. I had a similar task to solve and it's been taken care of very elegantly using that...

I am sorry to say but that is a very broad solution that covers only marginally what I wanted to find out.

Yes, I am thinking SET based, but there's much...

Excellent, that's what I need. One (that would be me) should explore those drop down menus better.

Thank you.

You really answered all my questions and shed some light.

Thanks a bunch for the efforts 🙂

Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and...

Nevermind my previous question, I just realized that I'm using INSTEAD OF when you were using FOR UPDATE.

I corrected the trigger definition, tested it and it seems to work.

Any reasons...

Ok, that I must be doing something wrong, because remember getting through this variant of the trigger and it doesn't do what it supposed to do.

Here is the table:

CREATE TABLE...

I'll try it in a sec, but I couldn't help it to ask, where's that inserted.ID coming from? Yes, I do have a PK in the table, but if the...

list partitioning by the first LETTER in the....

Spelling is difficult in the morning, before coffee.

Still no ideas at all?

Hm, yeah, I suspected that I'll get this kind of answer, which is not far from truth.

Basically, I have this approach, which I'm not sure it's correct. I have a...