Trigger Not Firing

Question

Trigger Not Firing

Viewing 5 posts - 16 through 20 (of 20 total)

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

Jim SSCrazy Eights Points: 8720 More actions · Answer 1

I have an update on this issue...

Just before I left for the holiday, I was able to see, using profiler, that the trigger was firing which left my mind in a state of pondering all weekend long. In a rare AH HA moment, I believe I realized what is going on. The trigger does work fine when we cause it to fire in SSMS - it does update all of the records where the month is greater than the month getting updated. The problem is with our application.

The page where this data is updated contains all 16 rows. When I change a single value, I made the assumption the application would only write that single value back to the database. Profiler shows that the application is issuing a single UPDATE statement for each of the sixteen rows when we click on save on that page. So, I have to assume that, for a very brief moment in time, the results of the trigger are successfully written but the subsequent UPDATE statements are overwriting those results with what is contained on the page when we click on save. Now I know what needs to be changed to make it work.

Thanks to everyone who provided feedback on this issue!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Thanks for letting us know what the problem was, Jim... I think you're on the right track.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

jim.powers (7/7/2008)
I have an update on this issue...
Just before I left for the holiday, I was able to see, using profiler, that the trigger was firing which left my mind in a state of pondering all weekend long. In a rare AH HA moment, I believe I realized what is going on. The trigger does work fine when we cause it to fire in SSMS - it does update all of the records where the month is greater than the month getting updated. The problem is with our application.
The page where this data is updated contains all 16 rows. When I change a single value, I made the assumption the application would only write that single value back to the database. Profiler shows that the application is issuing a single UPDATE statement for each of the sixteen rows when we click on save on that page. So, I have to assume that, for a very brief moment in time, the results of the trigger are successfully written but the subsequent UPDATE statements are overwriting those results with what is contained on the page when we click on save. Now I know what needs to be changed to make it work.
Thanks to everyone who provided feedback on this issue!

Jim -

For what it's worth: your trigger is designed to only take "input" from a single row (meaning - it's built entirely around a single-row update logic), so multiple changes to multiple rows in those critical columns will get lost (only one of them will go through, and there's no guarantee it will pick one of the rows where those values actually changed).....

If you do have multi-row updates going on - I'm not sure that the logic you have can be salvaged. The trigger may need to get scrapped altogether.

You're playing with running updates, so you should problem take a look at some of the options Jeff brought up in this article:

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/%5B/url%5D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

marc.dumortier Newbie Points: 9 More actions · Answer 4

Having multiple triggers (different condition) on the same table is not a problem but what could be one is having two trigger for the same condition on one table.

Not sure this is working. Probably he is firing the first trigger inserted and don't execute the second one. try to put all in one trigger

shivsen-572151 Valued Member Points: 61 More actions · Answer 5

I have imported the data into SQL Server 2000. The trigger didn't fire. Just a simple trigger to insert a data into the Audit table whener the data gets inserted into the main table. Can any one clarify that whether the trigger will fire if we do the 'Import'?.

Thanks