January 15, 2008 at 10:45 am
Hi folks,
I have what I think is a unique problem I'm trying to solve and unfortunately, my SQL Server experience is a bit limited.
I'm writing an application that needs to know when two things happen:
1. When a row is inserted into specific tables
2. When any row in specific tables is changed (and if possible, which column changed).
The method I would normally use would be to create INSERT and UPDATE triggers on my tables of interest and insert the ID number of the row that was added/changed into an audit table. This would give me the row that changed and the action (insert/update).
To my dismay, this technique causes very unexpected and very bad results on the application that primarily uses this database that I'm monitoring. I don't have the source for this application, but it's doing something that is locking the database only when my triggers are present. This problems happens when adding triggers to some tables, but not others. At this point, since the client app is a big black box to me, I'm giving up on this method and looking for something else that reliable.
Each table in this database has date/time stamps that are updated each time a row is changed, so my fall back plan is to poll the tables on a given interval to determine what changed since my last poll. This technique isn't favored for obvious reasons.
I'm hoping some of you SQL Server experts will have some other wiz-bang way to accomplish what I'm trying to do.
Thanks!
January 15, 2008 at 11:13 am
Well - I would tend to say the trigger IS in fact the right way to do it. Are you sure you don't want to try to figure out why it's causing you issues? The locking seems to point to the trigger taking a while to run: if you cut the execution time - then perhaps no locking issue anymore.
I mean - after all - that's one of the main reasons for triggers to begin with (auditing). You might need to make the trigger to fewer things, etc... or split the trigger into trigger+some kind of job to avoid whatever is causing the delay.
Anyway - how long is it taking for said trigger to run?
----------------------------------------------------------------------------------
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?
January 15, 2008 at 11:41 am
Hi Matt,
Thanks for the reply.
I've found that any database operation in my trigger causes this lock. I've tried something as simple as SELECT TOP 1 * FROM SysObjects, just to run some simple, quick query. That in fact locks up the database somehow. If I remove that trigger, all is fine.
I think somehow the client app is starting a transaction, my trigger is running in the context of that transaction, but somehow my trigger causes execution of that transaction to stop and hence the database is locked (I mean, I can't run ANY queries on the database when it's in this state). This 'lock' is removed when I kill the client app, which leads me to believe that it's an uncommitted transaction that hosing everything.
I would love to solve this issue so that I can continue with triggers. So any other suggestions would be great.
Thanks!
January 15, 2008 at 11:46 am
Matt - I should also add that I can take the exact same trigger code, add it to a different table in the same database, have the client app fire that trigger and all is fine. It's just certain tables that give me an issue, which again leads me to believe the client app is doing something strange that's causing this behavior when more triggers are added to some tables.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply