MySQL trigger to keep duplicate records from being entered

Question

I have a table that has a the following columns:

ID(primary key),
USER,
ACTION
TIME
LOCATION

I am looking to use a trigger or something that runs when the table is updated to insure that there are no duplicate entries based on the grouping of USER, ACTION, TIME, LOCATION. A preavious question was answered for me about how to clear the existing entries which resulted in:

DELETE t1.*
        FROM
  testlogins t1 INNER JOIN testlogins t2
  ON t1.user=t2.user
     AND t1.action=t2.action
     AND t1.time=t2.time
     AND t2.location=t2.location
     AND t1.id>t2.id;

This worked to clear the existing records. I thought i might be able to create a trigger based of this and I came up with this:

DELIMITER $$
create trigger del_duplicates
after insert
on test.testlogins for each row
begin
    DELETE t1.*
        FROM
  testlogins t1 INNER JOIN testlogins t2
  ON t1.user=t2.user
     AND t1.action=t2.action
     AND t1.time=t2.time
     AND t2.location=t2.location
     AND t1.id>t2.id;
end 
$$

However that returns:

Error Code: 1442. Can't update table 'testlogins' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I assume this is because I am asking the system to do a delete after a insert. I have also wondered if it would be worth while calling a stored procedure during a trigger to accomplish this.

What would be the best way to keep duplicate rows from being added that match in these four columns: USER, ACTION, TIME, LOCATION

Please see [this](http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice) question and see the accepted answer — Abhishek Ghosh, Apr 11 '15 at 16:44

score 0 · Accepted Answer · answered Apr 11 '15 at 16:44

The previous answer was about how to remove duplicates, but if you want to prevent duplicates to happen it's better to add a unique index:

CREATE UNIQUE INDEX idx_userlogins ON testlogins (USER, ACTION, TIME, LOCATION)

then to prevent an error to get raised when inserting a duplicated row, you could use this syntax:

INSERT INTO testlogins VALUES (value1, value2, value3, value4)
ON DUPLICATE KEY UPDATE user=user;

score 0 · Answer 2 · answered Apr 11 '15 at 16:44

0

CREATE UNIQUE INDEX body_idx ON testlogins (action, time, location);

This will prevent duplicates of (action, time, location) from existing.

answered Apr 11 '15 at 16:44

Havenard

23,249
4
31
59

score 0 · Answer 3 · answered Apr 11 '15 at 17:20

Like others have said, create a UNQUE INDEX (any field that will be unique for each user i.e. userName)

REPLACE INTO testlogins (`USER`, `ACTION`, `TIME`, `LOCATION`) VALUES (val1, val2, val3, val4)

replace into is a cool function that will replace if the unique index's match or insert if the unique index doesnt have a match

MySQL trigger to keep duplicate records from being entered

3 Answers3