Concurrency concerns with triggers

Question

I needed to create an auto-increment column based on the value of another column and I made the following trigger:

CREATE TRIGGER [dbo].[setPosition]
ON [dbo].[myTable]
AFTER INSERT
AS 
BEGIN
    DECLARE @maxPosition SMALLINT
    DECLARE @templateName VARCHAR(100)
    DECLARE @id INT

    SELECT @id=id FROM inserted
    SELECT @templateName=templateName FROM inserted
    SELECT @maxPosition=ISNULL(MAX(position),0) FROM [myTable] WHERE [templateName]=@templateName 

    UPDATE [myTable]
    SET position = @maxPosition+1 WHERE id=@id  
END

It works but I have a concern. Is there any chance I have double numbers? If, for example, it runs in an environment with many users. I have a similar case here where to ensure the previous concern does a loop check for error having first created an index and updates when there is no error. But I believe that such a check is futile because as far as I know the triggers run in isolated transaction mode.

UPDATED

The data after inserts would be like (id is an auto-increment column)

Answer 1

There are much better solutions for what you are trying to do. You could just assign an ID on the application/client-side first, or in the SQL code that does the insert. Or you could use a SEQUENCE per template. Or a random number. Or forget about the whole thing, and just number the rows when you query them.

---

But if you are really set on using a trigger, you need to take account of multiple or zero rows, and you need to take into account even multiple for the same templateName. You also need the correct locking on dbo.myTable.

So it should look like this.

CREATE TRIGGER dbo.setPosition
ON dbo.myTable
AFTER INSERT
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted)  -- early bailout
    RETURN;

UPDATE t
SET position = others.maxPosition + i.rn
FROM (
    SELECT *,
      -- need to number the inserts in case of multiple per templateName
      rn = ROW_NUMBER() OVER (PARTITION BY i.templateName ORDER BY i.id)
    FROM inserted i
) i
JOIN dbo.myTable t ON t.id = i.id
CROSS APPLY (
    SELECT
      maxPosition = ISNULL(MAX(position),0)
    FROM dbo.myTable others WITH (SERIALIZABLE, UPDLOCK)
    WHERE others.templateName = i.templateName
) others;

Ensure that your table is indexed on templateName or you will end up with major blocking and deadlocking issues.