3

When a trigger is being run is it possible to have non-zero different count of row in inserted and deleted tables (basically a mix of insert and updates being handled at the same time)?

For example, inserted table could have 2 rows and delete would have 1 row (matching 1 of the rows in inserted) and that would imply the trigger is handling a mix of 1 update (the matching rows in inserted and deleted tables) and 1 insert.

Update

Apparently the use of merge command might cause 1 trigger to run for all the changes done, see: Merge statement and after triggers on target table Or not, need to test this.

According to https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ the merge commands caused triggers to be run multiple times, separating updates and inserts.

Solution Update

Assuming this is the entire list of DML actions then there will never be the case when there will be mixed inserts and updates dealt in the same trigger run.

Community
  • 1
  • 1
Răzvan Flavius Panda
  • 21,730
  • 17
  • 111
  • 169

1 Answers1

1

No it isn't possible.

You can see how it works from the below.

CREATE TABLE T1
  (
     X INT
  )

INSERT INTO T1
VALUES     (1);

GO

CREATE TRIGGER TR
ON T1
FOR UPDATE, INSERT
AS
    SELECT (SELECT COUNT(*)
            FROM   inserted) AS InsertedCount,
           (SELECT COUNT(*)
            FROM   deleted)  AS DeletedCount

GO

MERGE INTO T1
USING (VALUES (1),
              (2),
              (3)) V(X)
ON T1.X = V.X
WHEN MATCHED THEN
  UPDATE SET X = V.X
WHEN NOT MATCHED THEN
  INSERT (X)
  VALUES (X);

Which returns two resultsets. One for the insert and one for the update.

enter image description here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I stated in the question that it does not work for `merge` command. Are you certain that there is absolutely no circumstance in which a trigger might have mixed updates and inserts? – Răzvan Flavius Panda Nov 18 '16 at 15:01
  • 1
    @RăzvanPanda - Yes. The only other language elements that fire triggers apart from merge are insert, update, delete so difficult to see why you would imagine they might cause this. triggers fire per statement and each statement apart from merge can only perform one action. – Martin Smith Nov 18 '16 at 15:05
  • There might be other cases though. I'll have to wait for other answers before voting/accepting to get more cetaintly. Also, need to try more things myself. – Răzvan Flavius Panda Nov 18 '16 at 15:27