How to check the actions of multiple rows in a SQL Server trigger?

Question

My kindergarten SQL Server taught me that a trigger can run with multiple rows in inserted and deleted pseudo tables. I mostly write startup code, keeping this in mind, often leading to some sort of cursor based room. Now I really can check them only one line at a time. How can I generate a multivibration trigger and will SQL Server actually ever send a multivibrator? Is it possible to set a flag so that SQL Server only triggers single-row triggers?

Answer 1

Yes, if an operator affects several lines, it should be handled by one trigger call, since you can return the entire transaction. It is not possible to split it into separate trigger calls logically, and I don't think SQL Server provides such a flag. You can force SQL Server to trigger a multiple-line trigger by issuing an UPDATE or DELETE statement that affects multiple lines.

Answer 2

Trigger definitions should always process multiple lines.

Taken from SQLTeam :

-- BAD Trigger code following: CREATE TRIGGER trg_Table1 ON Table1 For UPDATE AS DECLARE @var1 int, @var2 varchar(50) SELECT @var1 = Table1_ID, @var2 = Column2 FROM inserted UPDATE Table2 SET SomeColumn = @var2 WHERE Table1_ID = @var1 

The above trigger will only work for the last row in the inserted table.

Here's how you should implement this:

 CREATE TRIGGER trg_Table1 ON Table1 FOR UPDATE AS UPDATE t2 SET SomeColumn = i.SomeColumn FROM Table2 t2 INNER JOIN inserted i ON t2.Table1_ID = i.Table1_ID 

Answer 3

At first it seems to me that triggers process multiple lines with the cursor. Do not do this! Use static statistics, adding to inserted or deleted aliases instead. Someone put one of these cursor-based triggers on our database before I started working here. It took more than forty minutes to process a record of 400.00 records (and I often have to insert more than 100,000 records in this table for one client). Changing it to an installation solution changed the time by less than a minute. While all triggers must be able to handle multiple lines, you should not do this, creating a performance nightmare.

If you can write the selected status for the body, you can record the insert, update, or delete based on the same selected state that is set on the basis.

Answer 4

I always wrote my triggers for processing multiple lines, I understand that if one request inserted / updated / deleted several lines, then only one trigger will fire, and so you have to use the cursor to move the record one by one.

Answer 5

A single SQL statement always invokes a single trigger action — that part of the trigger definition. (This is also a circumstance that, at least once, causes everyone who writes the trigger.) I believe that you can find out how many entries affect the @@ ROWCOUNT check.