Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hsaid
Starting Member
2 Posts |
 Posted - 2009-09-14 : 13:14:10
|
| I need some help to figure out what condition would show the following problem, but first a little about my db config. Application running on XP with SQL 2K5 Express. Uses tableAdapters to perform database updates. SQL Trigger create a row whenever a table is changes using SQLCLR - transaction audit. The audits records are sent to central server for later processing. In one for our tests we noticed the SQL trigger created the 'deleted audit record) in the local sql express table but the underlying table rows did not delete. As the primary key of the table is GUID it can be assumed (maybe) that the application did perform a delete, followed by insert. However if the appl did delete, followed by an insert the SQL trigger should have also written an 'insert audit'. The only clue to the puzzle is that there were two deadlocks recorded about the same period by SQLCLR trigger code. Any possible suggestions will be greatly appreciated |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hsaid
Starting Member
2 Posts |
Posted - 2009-09-14 : 15:30:20
|
| The code in trigger is quite extensive and developed in c# to create a rows based upon TriggerContext. I not sure if looking thru 600+ lines of my code is worth someone else good use of time. I have an alternative senerio which could also explain the problem that I see.The application issues SQL UPDATE command, however my code in SQLCLR trigger thinks it is a 'delete' based upon triggerContext. And the problem is not that rollback or some other error condition is having a problem but TriggerContext or logic error in my code. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-14 : 17:00:00
|
| possibility 1) You have a logic problem in your C# code somewhere. Perhaps you can strip down your trigger to just the writing the log row just to make sure you are correctly interpreting the user's action (I/U/D).You're right that you'll probably have to work through that code on your own. But 600 lines of trigger code doesn't sound good to me. How long does a user transaction take? Why the CLR trigger? Do you have a lot of non-database stuff happening?Possibility 2)With all that custom code is it possible that the audit write is not happening under the same transaction context as the user action? If there was an error maybe the user transaction rolled back but the auditing transaction didn't. Your Deadlock may not be coincidental.Be One with the OptimizerTG |
|
|
|
|
|
|
Rollback and Triggers Puzzle