CODEWITHSUNDEEP
sql-servert-sqltriggers
stckvrflw
stckvrflw

Reputation: 1519

SQL Server Trigger that works - fires just once

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.

Upvotes: 2

Views: 3939

Answers (6)

Fandango68
Fandango68

Reputation: 4898

There is a better way.

Re-link the tables that your trigger created or altered, compare them against what's expected to be changed or added and avoid the trigger with a simple WHERE clause.

eg - this trigger I use to INSERT a record, but only once based on a column value existing (@ack).

DECLARE @ack INT
SELECT @ack = (SELECT TOP 1 i.CUSTOM_BOOL_1 AS [agent_acknowledged] FROM inserted AS i)
IF @ack = 1
BEGIN
    INSERT INTO TABLEA(
       COLA, COLB, etc
    )
    SELECT
       COLA, COLB, etc
    from inserted as i
        LEFT JOIN TABLEA AS chk --relink to the INSERT table to see if the record already exists
            ON chk.COLA = i.COLA
           AND chk.COLB = i.COLB
           AND etc
    WHERE chk.ID IS NULL  --and here we say if NOT found, then continue to insert
END

Upvotes: 0

Graham
Graham

Reputation: 639

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

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.

Upvotes: 1

Ben Hoffman
Ben Hoffman

Reputation: 8261

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.

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48542

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.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

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

Upvotes: 5

Related Questions