June 8, 2009 at 12:55 am
Dear All,
I have written a trigger on Update of a column in table.
When we update single row it works fine.
Now the Problem is that, everyday BULK UPDATE happen in this table,
and this trigger doesn't fire for each row. It gets fired only ONCE at the end of BULK UPDATE.
How can I fire this trigger for each row when a BULKUPDATE happen..?
Thanks in advance.
June 8, 2009 at 2:04 am
Hi ,
Triggers fire once per statement. Period. Thats what they do.
You will have to adapt your trigger to deal with multiple rows, or find a different way of achieving your aims.
June 8, 2009 at 5:10 am
No other way to fire it automaticaly for each row..?
June 8, 2009 at 5:43 am
Sorry - No.
By defintion triggers in SQl Server fire once per statement, and by examining the virtual tables inserted and deleted should be written to process all rows affected by the original statement. This is not normally difficult, just something that trigger developers need to be aware of.
Mike
June 8, 2009 at 5:44 am
In SQL Server triggers fire once per statement. There is no row trigger in SQL Server. The trigger fires once and all the affected rows are present in the inserted and/or deleted pseudo tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2009 at 11:45 pm
Thanks a lot.
Just one more doubt.
How can we utilise FIRE_TRIGGERS option.?
Has it to do something with the problem am facing.?
June 9, 2009 at 2:34 am
San (6/8/2009)
How can we utilise FIRE_TRIGGERS option.?
Fire triggers is a property of the BULK INSERT command and it controls whether or not insert triggers are fired for a bulk insert operation. If it's not specified the insert triggers do not fire at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2009 at 6:05 am
You can get a trigger to fire for each row by adding a cursor in the trigger. Doing this during a bulk load will really be sloooooooow.
April 3, 2010 at 3:48 am
Steve-3_5_7_9 (6/10/2009)
You can get a trigger to fire for each row by adding a cursor in the trigger.
You could do that...but it would be very silly and completely unnecessary 😛
As has been stated many times on this thread already, triggers fire once per statement.
All rows affected by the statement are available to trigger code in the inserted and deleted virtual tables. If a statement inserts 1000 rows, there will be 1000 rows in the inserted table.
Never use a cursor here, simply use a normal set-based DML statement to do whatever needs to be done.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 4:09 am
An example might help:
USE tempdb;
GO
-- Test table
CREATE TABLE dbo.Test
(
row_id INTEGER IDENTITY PRIMARY KEY,
data INTEGER NOT NULL,
created_dt DATETIME NULL
);
GO
-- Trigger to populate the created_dt column
-- (For demo only, a DEFAULT constraint would be better!)
CREATE TRIGGER trg__dbo_Test_AI
ON dbo.Test
AFTER INSERT
AS
BEGIN
SET XACT_ABORT ON;
SET NOCOUNT ON;
SET ROWCOUNT 0;
UPDATE T
SET created_dt = CURRENT_TIMESTAMP
FROM inserted INS
JOIN dbo.Test T
ON T.row_id = INS.row_id;
END;
GO
-- Insert 5 rows all at once
INSERT dbo.Test (data)
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500;
GO
-- Show the contents of the table
-- Notice the created_dt column is
-- populated for all 5 rows
SELECT *
FROM dbo.Test;
GO
DROP TABLE dbo.Test;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 4:18 am
What is the business case you need to deal with?
Basically I see two options:
1) if possible at all modify the trigger to deal with all rows at once or
2) import the data into a staging table, perform the required activity (set based, again) and move it to the final table.
I'm sure we can help you to decide which method would fit best here but you need to elaborate first what you're trying to do with each row.
April 3, 2010 at 11:51 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply