Trigger for preventing duplicates.

[kate s]

Im creating an employee database for TAFE. I have an employee table with employee_id and place fields.

The trigger is activated by the Before Insert event.

When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6.

I don't want a unique constraint.

The code is below:

CREATE TRIGGER employee_tg ON employee
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
 
IF NOT EXISTS (SELECT 1 FROM employee e
           INNER JOIN INSERTED i
           ON i.employee_id=e.employee_id
           AND i.place=e.place)
INSERT INTO employee(employee_id,place)
SELECT employee_id,place
FROM INSERTED
ELSE
RAISEERROR ('Duplicate employee_id 20 Place 6)
END IF;
END
 
DELIMITER;

Im having syntax problem in MYSQL. What could be wrong?

Any help would be appreciated!

[Atli]

Hi.

Are you sure this trigger was written for MySQL?
I ask because there are a number of things in there that don't exist in MySQL.
The RAISEERROR function, for instance, is a MSSQL thing if I am not mistaken and does not exists in MySQL.

You say that you do not want this duplicate error.
What is the purpose of this trigger then?
Could you not simply remove it altogether as well as the unique constraint on the key?

[kate s]

@Atli
Hi, I was researching through google and I found the RAISEERROR function to display the error message. I thought it will work for MYSQL. How can I display the error message then?

I have an employee table with employee_id and place fields. The trigger is activated by the Before Insert event.

The purpose of my trigger is prevent duplication in my table. When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6.

[Atli]

Ok. So you do want a unique constraint.
Then this makes a whole lot more sense :)

You don't really need a trigger for that.
A UNIQUE key would be far easier to use.

For example:

mysql> CREATE TABLE uniqueTest(
    ->   employee_id Int Not Null,
    ->   place VarChar(50) Not Null,
    ->   UNIQUE `uk_id_place` (employee_id, place)
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO uniqueTest VALUES (1, 'hello');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO uniqueTest VALUES (1, 'hello');
ERROR 1062 (23000): Duplicate entry '1-hello' for key 1
 

Is that what you need?

If you want to do this using a trigger, that is possible to.
And your code isn't far off.

There are a lot of errors in that code however.

First of all, you don't need to INSERT the data manually. Unless the trigger prevents it, the new data will be inserted automatically once the trigger has been executed.

The first line of your code has an extra ON clause that will cause an error.
Check out the CREATE TRIGGER syntax in the manual.

RAISEERROR is a non-standard function that is not used in MySQL.
Unfortunately the standard equivalent for it, the SIGNAL statement, won't be implemented until MySQL 5.2 (as far as I know).
Until then you will have to trigger an error by executing a statement that will cause an error.
I like using the CALL statement, using the error message as the procedure name.
Like:

mysql> CALL `Duplicate row inserted.`;
ERROR 1305 (42000): PROCEDURE skjol.Duplicate row inserted. does not exist
 

Using this inside a trigger would cause an error and effectively terminate the insert statement.

You would want your code to be something like:

CREATE TRIGGER triggerName
BEFORE INSERT ON targetTable
FOR EACH ROW
BEGIN
  IF /* The row already exists */ THEN
    CALL 'Can not insert duplicate row';
  END IF;
END
 

[kate s]

Thank you for your assistance!

The way you created the UniqueTest table and inserted the values and MYSQL complained with an error is exactly what I need!! But I want this using a Trigger.

I still have problems in the code I provided in post#1. I fixed the errors you stated but it still complains with an error.

Do you know of how to create the trigger after the 'IF' statement and assign the error messages an @message variable instead of using the CALL statement?

DELIMITER \\
 
CREATE TRIGGER employee_tg
BEFORE INSERT ON employee 
FOR EACH ROW 
BEGIN 
  IF /* The employee_id and place already exist*/ THEN 
    /*DISPLAY ERROR MESSAGE*/ 
  END IF; 
END 
 

Your help would be much appreciated!!

[kate s]

Hi Atli, I cam up with the following code:

DELIMITER //
 
CREATE TRIGGER employee_tg 
BEFORE INSERT ON employee
FOR EACH ROW
 
BEGIN
 
 
IF new.employee_id = employee_id
AND new.place = place
THEN
SET @messsage = 'duplicate employee_id and place';
 
ELSEIF new.employee_id = new.employee_id
AND new.place = new.place
THEN
SET @messsage = 'No duplication';
 
 
END IF;
END;//
 

I was just wondering if the code above prevents duplicate records?? If not how can I correct this?

Thanks