Prevent triggering multiple triggers at same time on table update

Question

In the SQL server, I have a table X and a table Y. The primary key of both is same say Z. I created a trigger that if anything is created/deleted/updated in X then

1. If the entry doesn't exist in Y for Z then create a new entry.
2. If an entry exists in Y for Z then update that row.

I ran a query like this:

Delete FROM Table2 where TId = 1;

This query deleted 10 rows and 10 triggers ran simultaneously. As all ran parallel every trigger executed the else block because there was no entry in Y initially for Z and as all are running they won't find the row in Y. So because of this 10 rows gets created in the Y table.

I want that I should get only 1 entry created and the other triggers should update that entry.

As example I have tables:

Table1(TId PRIMARY KEY, C12, C13);

Table2(C21 PRIMARY KEY, TId FOREIGN KEY(Table1, C11), C23);

Table3(TId PRIMARY KEY, C32, C33);

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg1] ON  [dbo].[TABLE2] AFTER DELETE
        AS
        BEGIN
        SET NOCOUNT ON
            IF EXISTS (SELECT * FROM Deleted D )
                    BEGIN
                        IF EXISTS (Select * from [dbo].[Table3] where TId in (Select TId from Deleted D) )
                            BEGIN
                                Update [dbo].[Table3] SET C32 = 1 where TId in (Select TId from Deleted D);
                        END
                        ElSE
                            BEGIN
                            INSERT INTO [dbo].[Table3]
                                (
                                    TId,
                                    C32,
                                    C33
                                )
                            SELECT  TId, 
                                    3,
                                    GETUTCDATE()
                            FROM Deleted D WHERE TId is not null
                            SET NOCOUNT OFF
                        END

            END
        SET NOCOUNT OFF
    END
            
PRINT '';
PRINT 'End of script';
PRINT ' --- // ---';

Answer 1

Your entire trigger's code should be something like this:

MERGE INTO Table3 t
USING (SELECT TId,CASE WHEN COUNT(*) > 1 THEN 1 ELSE 3 END as C32
       FROM deleted
       GROUP BY TId) u
ON t.TId = u.TId
WHEN MATCHED THEN UPDATE SET C32 = 1
WHEN NOT MATCHED AND u.TId is not null THEN
    INSERT (TId,C32,C33) VALUES (u.TId, u.C32, GETUTCDATE());

Which will insert a new row but decide whether to set C32 to 1 or 3, depending on how many rows in DELETED are for the same TId, and just update C32 to 1 if a row already existed.

This is all about thinking in sets. You weren't accounting for the fact that deleted could contain multiple rows, some or all of which may have the same TId value. You don't write IF/ELSE blocks that can only make a single decision for all rows in deleted¹.

---

¹E.g. if deleted had contained 4 rows total, 2 for TId 6 and 2 for TId 8, and Table3 had contained a row for TId 6 but no row for TId 8, your trigger would have found some matching rows in Table3 and just performed the UPDATE.