Insert Trigger

  • Hi All,

    I have table A that has 2 columns(col 1,col2). I would like a create trigger that collects all duplicate inserts.For example ,when i insert new records that both col 1and col2 exists on Table A ,then insert that record into Table Audit as well.

    Here what came up with ,but logic is not right.

    Create TRIGGER [dbo].[Dupl_Rec]

    ON [dbo].[Table A]

    FOR INSERT AS

    IF EXISTS(SELECT A.col1,A.Col2 FROM TableA A, Inserted i WHERE A.Col1==i.Col1 AND A.Col2=i.Col2)

    BEGIN

    INSERT INTO Audit

    (

    EVENT_DATE,

    Col1,

    Col2)

    SELECT

    GETDATE(),

    ins.Col1,

    ins.Col2

    FROM Inserted ins

    END

    Thank you

  • Try below.  Sorry, I don't have time right now to explain the code.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO

    CREATE TRIGGER [dbo].[Dupl_Rec]
    ON [dbo].[Table A]
    AFTER INSERT
    AS
    SET NOCOUNT ON;

    INSERT INTO dbo.Audit (
    EVENT_DATE,
    Col1,
    Col2
    )
    SELECT DISTINCT i.EVENT_DATE, i.Col1, i.Col2
    FROM inserted i
    INNER JOIN dbo.TableA A ON
    A.Col1=i.Col1 AND
    A.Col2=i.Col2 AND
    A.EVENT_DATE < (SELECT MIN(i.EVENT_DATE) FROM inserted i)

    /*end of trigger*/
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • USE master;
    GO
    DROP DATABASE IF EXISTS TestDB;
    GO
    CREATE DATABASE TestDB;
    GO
    USE TestDB;
    CREATE TABLE dbo.Table_A
    (
    col1 INT,
    col2 INT
    );

    CREATE TABLE dbo.Audit
    (
    EventDate DATETIME2 NOT NULL
    CONSTRAINT DF_Audit_EventDate DEFAULT(SYSDATETIME()),
    col1 INT,
    col2 INT
    );
    GO
    CREATE TRIGGER dbo.Dupl_Rec ON dbo.Table_A
    AFTER INSERT
    AS
    BEGIN
    IF EXISTS(SELECT Table_A.col1,
    Table_A.col2
    FROM Table_A INNER JOIN INSERTED
    ON Table_A.col1 = INSERTED.col1 AND
    Table_A.col2 = INSERTED.Col2
    GROUP BY Table_A.col1, Table_A.col2
    HAVING COUNT(*) > 1)
    BEGIN
    INSERT INTO Audit (Col1, Col2)
    SELECT INSERTED.col1,
    INSERTED.col2
    FROM INSERTED
    WHERE EXISTS (
    SELECT Table_A.col1,
    Table_A.col2
    FROM Table_A
    WHERE Table_A.col1 = INSERTED.col1 AND
    Table_A.col2 = INSERTED.col2
    GROUP BY Table_A.col1, Table_A.col2
    HAVING COUNT(*) > 1)
    END;
    END;
    GO
    INSERT INTO dbo.Table_A VALUES
    (1, 3);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- no dup
    (4, 7),
    (5, 6),
    (1, 5);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- 1 dup
    (1, 3);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- 2 dup
    (4, 7),
    (1, 5);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- 1 dup and 1 new
    (5, 6),
    (11, 15);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- 1 already dup, so 3 rows in Table_A with 1, 3
    (1, 3);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- more rows for test
    (11, 20),
    (12, 21),
    (13, 22),
    (14, 23);
    GO
    INSERT INTO dbo.Table_A VALUES -- 3 dups and 3 new
    (11, 20),
    (12, 21),
    (13, 22),
    (22, 23),
    (24, 23),
    (25, 23);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    INSERT INTO dbo.Table_A VALUES -- dup in same insert and 4 rows in Audit with 11, 20
    (11, 20),
    (11, 20),
    (11, 20),
    (11, 20);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO
    CREATE OR ALTER TRIGGER dbo.Dupl_Rec ON dbo.Table_A
    AFTER INSERT
    AS
    BEGIN
    IF EXISTS(SELECT Table_A.col1,
    Table_A.col2
    FROM Table_A INNER JOIN INSERTED
    ON Table_A.col1 = INSERTED.col1 AND
    Table_A.col2 = inserted.Col2
    GROUP BY Table_A.col1, Table_A.col2
    HAVING COUNT(*) > 1)
    BEGIN
    INSERT INTO Audit (Col1, Col2) -- if only one insert in Audit if dup in same statement
    SELECT Table_A.col1,
    Table_A.col2
    FROM Table_A INNER JOIN INSERTED
    ON Table_A.col1 = INSERTED.col1 AND
    Table_A.col2 = inserted.Col2
    GROUP BY Table_A.col1, Table_A.col2
    HAVING COUNT(*) > 1
    END;
    END;
    GO
    INSERT INTO dbo.Table_A VALUES -- dup in same insert, but only one row with 40, 66 in Audit
    (40, 66),
    (40, 66),
    (40, 66),
    (40, 66);
    GO
    SELECT *
    FROM dbo.Table_A;

    SELECT *
    FROM dbo.Audit;
    GO

    This is a solutions with 2 different rules for insert in Audit. Hope it helps you!!!! The solution with insert of all dup-rows could be, if col1 and col2 tells that it's a duplicate, but there are other columns, which values could be different.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply