Errors in SQL SERVER triggers

Errors in SQL SERVER triggers

There is an after insert trigger that triggers several times when the same transaction inserts more than one data. How many records are there in the inserted staging table?

Myth: Within the same transaction, there must be only one trigger, insert a few according to the inserted table there are several

The actual situation is not true, to divide the situation, look at the INSERT INTO statement in the transaction how to write

Situation one.

BEGIN TRAN I

Nsert into T1 (field1,field2) VALUE (v001,v002);

INSERT into T1 (field1,field2) VALUE (v001,v002);

COMMIT

Result: Triggered two times, the inserted table has only one record

Situation two.

BEGIN TRAN

INSERT into T1 (field1,field2) VALUES (v101,v102), (v201,v202);

COMMIT

Result: Trigger once, inserted table has two records

Situation three.

BEGIN TRAN

INSERT into T2 SELECT ID, name, address from T1

COMMIT

Result: Trigger once, inserted table has multiple records

Conclusion: The number of triggers is independent of the same transaction and is related to multiple inser into directives

Errors in SQL SERVER triggers