January 15, 2018 at 3:38 pm
I have a table with one trigger on it.
CREATE TRIGGER [dbo].[trgPersonnel] ON [dbo].[Personnel]
FOR UPDATE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
IF TRIGGER_NESTLEVEL() > 1
RETURN
DECLARE @User varchar(100)
DECLARE @AuditDate smalldatetime
SET @User = suser_sname()
SET @AuditDate = getdate()
INSERT Personnel_Audit
SELECT
AuditType = 'U',
AuditDate = @AuditDate
EditedBy = @User,
PersonellID,
Forename,
Surname
FROM Deleted
Every time one row in the dbo.Personnel table is updated (and PersonnelID is the Primary Key and it is being done in a procedure with WHERE PersonnelID = @PersonnelID) - 3 identical rows appear in table Personnel_Audit.
I really just want one row to record each update, not 3. Any help much appreciated.
January 15, 2018 at 4:11 pm
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 16, 2018 at 6:54 am
TheSQLGuru - Monday, January 15, 2018 4:11 PM1) 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.
January 16, 2018 at 5:48 pm
webskater - Tuesday, January 16, 2018 6:54 AMThe time difference is nothing for the first two records and a couple of milliseconds for the third...
I guess it is 3ms. You can use datetime2 type for a higher precision.
Check how many times the procedure executes using SQL Server Profiler or Extended Events.
January 16, 2018 at 7:29 pm
webskater - Tuesday, January 16, 2018 6:54 AMTheSQLGuru - Monday, January 15, 2018 4:11 PM1) 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply