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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy