SQL Server Trigger that works - fires just once

Question

Hello all,

I want to do some calculations when my table data is changed. However, I am updating my table manually and copy pasting about 3000 rows in once. That makes my trigger work 3000 times, but I want it to do the trigger only once.

Is there a way to do that ?

Thanks.

Answer 1

What's your statement?

If you have multiple inserts then it will fire for each insert you are running. If you want it to execute only once for multiple inserts you must:

1. Write your insert on a single statement such as insert into foo select * from bar
2. Your trigger can't be for each row

Other possibility might be:

1. Disable the trigger
2. Perform your insertions
3. Put the trigger code in a stored procedure
4. Run your stored procedure

Answer 2

Your trigger will not fire 3000 times if you are modifying 3000 rows in a single statement. Your trigger will fire once and there will be 3000 rows in your virtual 'deleted' table.

Answer 3

The issue is caused because you are manually pasting the 3000 rows. You really have 2 solutions. You can turn off the trigger by doing this:

ALTER TABLE tablename DISABLE TRIGGER ALL 
-- do work here 
ALTER TABLE tablename ENABLE TRIGGER ALL

and then run the contents of your trigger at then end or you can put your 3000 columns into a temp table and then insert them all at once. This will only setup 1 trigger. If this isn't enough please give us more info on what you are trying to do.

Answer 4

If by 'manually' you mean you are copying and pasting into some User Interface tool (like an Access dataGrid) or something like that, then the tool may be issuing one insert statement per row, and in that case you are out of luck the database trigger will be executed once per insert statement. As other answers have mentioned, if you can insert the rows directly into the database, using a single insert statement, then the trigger will only fire once.