Audit Trigger creating 3 records

Transact-SQL
1

I have a trigger to INSERT into an audit table. Looking a few months back, it seems that the trigger was working properly, but now it is creating 3 identical records. Here is what the trigger is doing:

ALTER TRIGGER [dbo].[trg_audit_member] ON [dbo].[Member]
AFTER INSERT,UPDATE,DELETE
AS
SET NOCOUNT ON
BEGIN
INSERT dbo.audit_member
([MemberID]
,[LastName]
,[AuditID]
)
SELECT [MemberID]
,[LastName]
,[AuditID]
FROM inserted

END

Any idea why this is creating 3 records? I did an update through management studio and it created 3, as it does when something is done through the app.

Thanks.

2

If it is a simple insert, this should not happen. Couple of things to check:

  1. Check if there is more than one trigger on the table that does the same thing.

  2. Assuming that the code you have shown is the whole code, nested triggers or recursive triggers should not play a part here, but if there is more to the code than what you have shown, check if nested triggers is causing this behavior. Nested trigger feature is enabled by default.

3

Have you got another trigger on the table (which is re-saving the data)? This will list them

SELECT TOP 100
	[TableSchema] = S.name, 
	[TableName] = T.name,
	[TriggerName] = O.name
FROM	sys.tables AS T
	JOIN sysobjects AS O
		 ON O.parent_obj = T.object_id
		AND O.type = 'TR' 
	JOIN sys.schemas AS S
		 ON S.schema_id = T.schema_id
WHERE	    T.[name] = 'Member'
ORDER BY [TableName], [TableSchema], [TriggerName]
4

Ah, that's it. There is another trigger on the table that performs two updates. Thanks guys!