Triggers Basic

Question

Post reply

Triggers Basic

naveenreddy.84

Ten Centuries

Points: 1252
More actions
June 1, 2009 at 2:52 pm

#138185

Hi,
Can anyone explain me how the triggers work?
Scenario: I have created a Trigger on a table to carry out certain transaction, after every insertion happens to the table. If 10 rows in the table gets inserted at the same time, how will the trigger gets fired.
Will there be only one trigger instance that will be fired?
Or
Will there be 10 different instance of the trigger that will be fired?
I need to implement a trigger that gets fired for each row being inserted into the table
Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alberto dbLearner Ten Centuries Points: 1246 More actions · Answer 1

Start here:

http://msdn.microsoft.com/en-us/library/ms178110(SQL.90).aspx

Good luck.

Alberto

Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader

GSquared SSC Guru Points: 260824 More actions · Answer 2

The trigger will fire once. It's absolutely critical that triggers be built so they can efficiently handle multiple rows.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Alberto dbLearner Ten Centuries Points: 1246 More actions · Answer 3

or here:

http://msdn.microsoft.com/en-us/library/ms191524(SQL.90).aspx

Alberto

Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

Hi,

A trigger gets fired once for every sql batch it is assigned.

In example, it you do something like this :

Insert into table A (ID, text)

select top 10 ID, text from AnotherTable

Then the trigger will be fired just once and will have 10 rows in the "Inserted" table. Your code will need to manage the usage of the 10 rows 1 by one, but I don't think you will need to do a 1 by one scenario.

So, keep in mind a trigger can be triggered with multiple input rows, and your code needs to handle these scenarios.

If you have more questions, post some code here, and someone will try to help you. Be nice, and post the DDL also, so we can easily reproduce your solution.

Hope that helps,

Cheers,

J-F

Lowell SSC Guru Points: 323518 More actions · Answer 5

the trigger fires one for any statement which affects the table; as a result, any statement you write inside that trigger must plan on updating/inserting multiple rows. it CANNOT assume that the trigger will fire for each row that got inserted or updated for example.

a classic example would be a trigger which updates a datetime column to getdate() any time the UPDATE event occurs.

inside the trigger, you have access to two special tables named INSERTED and DELETED; they of course have the same columns as the table the trigger is created on. during an update, the INSERTED virtual table has all the new values that are replacing the previous values, which can be found in the DELETED table.

so an update statement typically would join the real table to the virtual, to make sure you change every row that was affected by the update statement.

the trigger might look like this for example:

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT,UPDATE

AS

UPDATE WHATEVER

SET UPDATEDDT = GETDATE()

FROM INSERTED

WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!