1) Don't declare variables and put stuff into them and then use in SELECT like that. Just put the system function calls in the SELECT.
2) Are you 100% positive that there are no other triggers (on this or any other table), or any other code, that could be putting rows into the audit table?
3) Are you 100% positive that only ONE update is being done to the row that appears three times in the audit table?
4) I cannot see any possible way for their to be duplicate rows other than 2 and 3 above.
Thank you for you reply. As a matter of curiosity - what is wrong with 1)?
It was, I am afraid, no 3). The front end developer swore he was only calling a procedure once but it turns out he calls 3 stored procedures, one after the other, which all update the table. The time difference is nothing for the first two records and a couple of milliseconds for the third. I have to say I didn't write this - I've just stuck an audit table on the table and some triggers to try to find out what is going on. There is no error catching in the stored procedures - just blind faith in the front end that all 3 execute correctly one after the other.
#1 is simply inefficient and thus and poor coding. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service