CODEWITHSUNDEEP
sqlsql-server
whoisearth
whoisearth

Reputation: 4160

SQL Server update trigger executing twice

I have the following trigger - 

USE [DatabaseA]
GO
/****** Object:  Trigger [dbo].[T_TableA_U]    Script Date: 02/17/2014 18:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_TableA_U]
on [dbo].[TableA]
after update
as
    set nocount on
    DECLARE @HistoryType    char(1) --"I"=insert, "U"=update, "D"=delete
    DECLARE @RevisionID INT
    SET @HistoryType = 'U'
    SET @RevisionID = 0


    INSERT INTO [DatabaseB].[dbo].[TableA]
            (column1_revtm, column2_revtype,
            column3_id, column4_revid, column5_type, ....)
        SELECT
            GETDATE(), @HistoryType,
            a.column1_id, @RevisionID, a.column2, ....
            FROM TableA a
            inner join inserted i on a.column1_id = i.column1_id

If I manually update a row in the SOURCE it creates 1 new row in the DESTINATION. This is good.

When I use the 3rd party application I'm building this trigger on however, it's generating duplicate rows in the DESTINATION. All data is exactly the same except for the GETDATE() which tells me it's somehow duplicating the result which the application is forcing.

So how do I get around this? Is there a way to force a DISTINCT before the INSERT happens either in this trigger or could I create another trigger on the DESTINATION table that says if the row is a duplicate then only INSERT 1 of them?

Upvotes: 1

Views: 4266

Answers (1)

whoisearth
whoisearth

Reputation: 4160

I found the problem to be in the way the legacy application is designed. The table I had this trigger on had a PK/FK matching to another table. I reversed the trigger to run off the table with the FK and it works on a 1 to 1 basis with no duplicates so it would seem the legacy app updates both tables even if only 1 table is updated.

Upvotes: 3

Related Questions