September 21, 2020 at 4:56 pm
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
September 21, 2020 at 5:21 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 23, 2020 at 11:16 am
USE master;
GO
DROP DATABASE IF EXISTS TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.Table_A
(
col1INT,
col2INT
);
CREATE TABLE dbo.Audit
(
EventDateDATETIME2 NOT NULL
CONSTRAINT DF_Audit_EventDate DEFAULT(SYSDATETIME()),
col1INT,
col2INT
);
GO
CREATE TRIGGER dbo.Dupl_Rec ON dbo.Table_A
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECTTable_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)
SELECTINSERTED.col1,
INSERTED.col2
FROM INSERTED
WHERE EXISTS (
SELECTTable_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(SELECTTable_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
SELECTTable_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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply