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.
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.
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:
insert into foo select * from barOther possibility might be:
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.
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.
If you are limited in how you can import the data into the system that does a single insert per row, I would suggest that you import data into an intermediate table then do the insert into the final table from the intermediate one.
