Trigger to Prevent Insertion for Duplicate Data of Two Columns

Trigger to prevent Insertion for duplicate data of two columns

Something like this:

CREATE TRIGGER MyTrigger ON dbo.MyTable
AFTER INSERT
AS

if exists ( select * from table t 
    inner join inserted i on i.name=t.name and i.date=t.date and i.id <> t.id)
begin
    rollback
    RAISERROR ('Duplicate Data', 16, 1);
end
go

That's just for insert, you might want to consider updates too.

Update

A simpler way would be to just create a unique constraint on the table, this will also enforce it for updates too and remove the need for a trigger. Just do:

ALTER TABLE [dbo].[TableName]    
ADD CONSTRAINT [UQ_ID_Name_Date] UNIQUE NONCLUSTERED
(
    [Name], [Date]
)

and then you'll be in business.

Prevent duplicate data in using After Insert Trigger

If you are unable to put a constraint in place, then you need to handle the fact that Inserted may have multiple records. And because its an after insert trigger, you don't need to do anything if no duplicates are found because the records are already inserted.

ALTER TRIGGER [dbo].[SDPRawInventory_Dup_Trigger] 
ON [dbo].[SDPRawInventory]
AFTER INSERT  
AS 
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM dbo.SDPRawInventory S
        INNER JOIN Inserted I ON
            -- Test for a duplicate
            S.InvYear = I.InvYear
            AND S.InvMonth = I.InvMonth
            AND S.SDPGroup = I.SDPGroup
            -- But ensure the duplicate is a *different* record - assumes a unique ID
            AND S.ID <> I.ID
    )
    BEGIN
        THROW 51000, 'Duplicate data.', 1; 
    END;
END;

Note the simplified and modern error handling.

EDIT: And if you have no unique key, and no permission to add one, then you need an instead of trigger to only insert non-duplicates e.g.

ALTER TRIGGER [dbo].[SDPRawInventory_Dup_Trigger] 
ON [dbo].[SDPRawInventory]
INSTEAD OF INSERT  
AS 
BEGIN
    SET NOCOUNT ON;

    -- Reject the entire insert if a single duplicate exists
    -- Note if multiple records are inserted, some of which are duplicates and some of which aren't, they all get rejected
    IF EXISTS (
        SELECT 1
        FROM dbo.SDPRawInventory S
        INNER JOIN Inserted I ON
            -- Test for a duplicate
            A.InvYear = I.InvYear
            AND A.InvMonth = I.InvMonth
            AND A.SDPGroup = I.@SDPGroup
    )
    -- Test that Inserted itself doesn't contain duplicates
    OR EXISTS (SELECT 1 FROM Inserted GROUP BY InvYear, InvMonth, SDPGroup HAVING COUNT(*) > 1)
    BEGIN
        THROW 51000, 'Duplicate data.', 1; 
    END;

    INSERT INTO dbo.SDPRawInventory (SDP_SKU_DESC, WholeQty, InvYear, InvMonth, SDPGroup, invUOM, LooseQty)
      SELECT SDP_SKU_DESC, WholeQty, InvYear, InvMonth, SDPGroup, invUOM, LooseQty
      FROM Inserted I 
      WHERE NOT EXISTS (
          SELECT 1
          FROM dbo.SDPRawInventory S
          -- Test for a duplicate
          WHERE S.InvYear = I.InvYear
          AND S.InvMonth = I.InvMonth
          AND S.SDPGroup = I.SDPGroup
      );
END;

Note: This doesn't do anything to handle existing duplicates.

Why does my trigger to avoid duplicate rows not work?

This is happening because it is detecting the record you're currently inserting to the table. You need to filter that out of the EXISTS clause:

CREATE TRIGGER LogDuplicates ON bkPersonPoints
FOR INSERT
AS
if exists (select * from bkPersonPoints c 
                    inner join inserted i 
                        on c.Name = i.Name 
                        and c.Points = i.Points
                        and c.id <> i.id)
begin
    rollback
end
GO

How do I implement a trigger in MYSQL to prevent insertion when row has duplicate data?

You are right - check constraints are are supported in DDL in MySQL, but the are ignored.
From the reference - The CHECK clause is parsed but ignored by all storage engines.

The solution is to create a BEFORE INSERT trigger, check new values and throw an error from the trigger if needed.

To raise the error you can use:

• SIGNAL statement: SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'hot_sid = not_sid';
• call nonexistent stored procedure: CALL proc_error();

Insert trigger preventing duplicates

As @Qpirate mentions, you should probably put some sort of UNIQUE constraint on the column. This is probably 'stronger' than using a trigger, as there's ways to disable those.

Also, the implicit-join syntax (comma-separated FROM clause) is considered an SQL anti-pattern - if possible, please always explicitly declare your joins.

I suspect that your error is because your trigger seems to be an AFTER trigger, and you check to see if there are any (non-zero) rows in the table; in other words, the trigger is (possibly) 'failing' the INSERT because it was INSERTed. Changing it to a BEFORE (or INSTEAD OF) trigger, or changing the count to >= 2 may solve the problem.

Without seeing your insert statement, it's impossible to know for sure, but (especially if you're using a SP), you may be able to check for existence in the INSERT statement itself, and throw an error (or do something else) if the row isn't inserted.

For example, the following:

INSERT INTO tbl1 (identificationCode, *otherColumns*)
VALUES (@identificationCode, *otherColumns)
WHERE NOT EXISTS (SELECT '1'
                  FROM tbl1
                  WHERE identificationCode = @identificationCode)

Will return a code indicating 'row not found' (inserted, etc; on pretty much every system this is SQLCODE = 100) if identificationCode is already present.

How can I prevent duplicate entries over multiple columns?

If you're using MySQL 8.0.16 or newer, you can use a CHECK constraint.

CREATE TABLE `FOLLOWERS` (
    `FOLLOWER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CHECK (FOLLOWER_ID != FOLLOWING_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

If you're using an older version, see Can a MySQL trigger simulate a CHECK constraint? for how to emulate check constraints with triggers.

Prevent duplicates with trigger SQL

You should really use a constraint. An "after insert" trigger will actually put the second row in the table . . . and hopefully no one is using NOLOCK for reading it.

In any case, you have to actually count the rows and look for multiple occurrences. It would be something like this:

Create trigger tr_Duplicate on Utentes after INSERT as
begin    
    if exists (select 1
               from utentes u join
                    inserted i
                    on u.nic = i.nic
               group by u.nic
               having count(*) > 1
              )
    begin
        print 'NIC already in database';
        rollback;
    end;
end;

With an instead of trigger, you would not add new rows into the table if one already exists:

create trigger tr_Duplicate on Utentes after INSERT as
begin    
    if exists (select 1
               from utentes u join
                    inserted i
                    on u.nic = i.nic
              )
    begin
        print 'NIC already in database';
        rollback;
    end;
    else
    begin
        insert into utentes
            select i.*
            from inserted i;
    end;
end;

sql trigger to stop duplicates across row

Create trigger

CREATE TRIGGER dbo.uniqueUserQuestion 
ON dbo.submit_Answer
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS 
    (
        SELECT 1 
        FROM dbo.submit_Answer T 
        INNER JOIN INSERTED I 
        ON T.user_name = I.user_name 
            AND T.question_id = I.question_id
    )
    BEGIN
        -- Do dupe handling here
        PRINT 'duplicate'
        raiserror('cant submit answer to same question twice')
        return
    END

    -- actually add it in
    INSERT INTO
        dbo.submit_Answer
    SELECT
        *
    FROM
        INSERTED I
END
GO

Raise trigger for duplicate prevention

First : you forget a ROLLBACK statement to cancel the transaction

Second : you forget to count (HAVING)

Third : you do no have the right syntax for RAISERROR

The code must be :

CREATE OR ALTER TRIGGER prevent_recast 
ON movie_cast$ 
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF NOT EXISTS (SELECT *
               FROM   movie_cast$ as t
                      JOIN inserted i 
                         ON i.mov_id = t.mov_id
                            AND i.act_id = t.act_id
                            AND i.role = t.role
               HAVING COUNT(*) = 1)
RETURN;
ROLLBACK;
RAISERROR('Duplicate Data : this actor is already cast for this movie.', 16, 1);
GO

Of course as @Larnu says, this is a stupid thing to do a cancel on a transaction that is made of interpreted code (Transact SQL) and runs after the INSERT, instead of using a UNIQUE constraints that runs in C language and acts before the insert !

The constraint will be as simple as:

ALTER TABLE movie_cast$
   ADD UNIQUE (actor_id, mov_id, role_name);

Please DO NOT modify my code... Just suggests some corections

How to prevent a trigger from firing so that duplicate data is not entered into a table

Try it with LEFT JOIN to check if same record already exist, and make sure to have a code that works on multiple rows as @Lamu suggested to you in comments.

Something like this:

ALTER TRIGGER [dbo].[setCorrectGenreAfterUpdate]
   ON  [dbo].[authorGenres]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO booklibrarys(bookLibraryID, parentBookID, genreID)
    SELECT NEWID(), bl.bookId, ins.genreID FROM Inserted ins
    INNER JOIN booklist bl ON bl.authorID = ins.authorID -- this is to get  bookId for each row, not for one like in variable
    LEFT JOIN booklibrarys blib ON blib.genreID = ins.genreID AND bl.bookId = 
blib.parentBookID
    WHERE blib.bookLibraryID is NULL
END

---

Related Topics