Trigger to fire after multiple update statements

Question

Trigger to fire after multiple update statements

sarah_saggi432

SSC Rookie

Points: 29
More actions
August 14, 2008 at 1:16 am

#382241

I want to write a trigger that fires after multiple update statement:
UPDATE salary_elements
SET element_value=999
WHERE salary_id=0001
UPDATE salary_elements
SET element_value=888
WHERE salary_id=0002...& so on
But, after every update statement the trigger is fired.
How do I get the trigger to fire after all the update statements in SQL server 2000

Viewing 8 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Triggers fire after every update. That's how they work. You can put code into the triggers so that they don't do whatever they do in certain cses, but that's all.

Perhaps explain a bit more about what you're doing and the reasons and maybe someone can suggest an alternative.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sarah_saggi432 SSC Rookie Points: 29 More actions · Answer 2

Hey, thanks for the quick response.

The trigger is supposed to fire after all the salary elements have been updated like the basic salary, housing allowance, transportation allowance etc...& a mail is to be sent with the updated salary.

The table has the column:employee,

element_value,

salary_id

If the salary_id is 0001,it is basic salary,if 0002 it is housing allowance & so.on

So, in order to update all these salaries I have to write multiple statements But, I need the trigger to sent a single mail after all/some of these salaries have been updated for an employee .

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Perhaps write a stored procedure, keep track of the employees that you've updated in a temp table and then send the mail at the end of the procedure.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sarah_saggi432 SSC Rookie Points: 29 More actions · Answer 4

ok..one more question.

How do I keep logging all the updates into temporary table.

the 1st update should be the first row of the temp table,

the 2nd update the 2nd row & so on..

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Depends on your table structure and what and who you need to mail. Put into the temp table enough info that you can then identify which salaries you updated for when you later send the mail.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I would create a permanent table with a processed flag and insert salary changes to it from the trigger. Then have a job that runs every n minutes that processes the rows in this table, emails them, and marks them as processed.

It may not be as big a deal with Database Mail in 2005 as it was in prior versions with SQL Mail, but it is generally not a good idea to put tasks like emailing or processes that are outside the database in a trigger as if that outside process fails your entire statement (update, insert, delete) will fail and rollback.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 7

Is there a reason to not do all the updates in one command?

- 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