Trigger for preventing duplicates (Please Help)

MaryH · #1 (**permalink**) 12-03-08, 19:35

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:

Code:

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!

r937 · #2 (**permalink**) 12-03-08, 21:23

maryh, you must have a twin brother, larry s, in the same course --

http://www.sitepoint.com/forums/showthread.php?t=587005

shammat · #3 (**permalink**) 12-04-08, 03:26

Quote:

Originally Posted by MaryH

I don't want a unique constraint.

May I ask why?
That is - pardon me - a very stupid "requirement".

This is exactly what primary keys and unique constraints were made for.

Any solution you build yourself is going to be less scalable and robust than the implementation in the database.

r937 · #4 (**permalink**) 12-04-08, 03:28

shammat, it's a homework assignment, that's why it has to be a trigger

shammat · #5 (**permalink**) 12-04-08, 03:44

Quote:

Originally Posted by r937

shammat, it's a homework assignment, that's why it has to be a trigger

The the teacher should be replaced. It's a stupid and senseless assignment.

Triggers should not be used to duplicate features that the database already has.

gvee · #6 (**permalink**) 12-04-08, 04:55

Does this work? Can't test it syntactically but it should be close methinks

Code:

CREATE TRIGGER employee_tg ON employee
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN

DECLARE _nzdf varchar(500);
    SET _nzdf = 'CREATE UNIQUE INDEX nzdf ON employee (employee_id);';

IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.STATISTICS
               WHERE  table_name = 'employee'
               AND    index_name = 'nzdf') THEN
  EXECUTE _nzdf;
END IF;

DELIMITER;

healdem · #7 (**permalink**) 12-04-08, 05:53

Quote:

Originally Posted by shammat

The the teacher should be replaced. It's a stupid and senseless assignment.

Triggers should not be used to duplicate features that the database already has.

if the teacher is trying to teach pupils to use triggers to check for duplicates I'd agree, however if the teacher is using this a method of learning how to use triggers then I'd disagree.. it may well be a suitable way of expressign concepts and ideas behind triggers

MaryH · #8 (**permalink**) 12-04-08, 06:03

Quote:

Originally Posted by georgev

Does this work? Can't test it syntactically but it should be close methinks

Code:

CREATE TRIGGER employee_tg ON employee
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN

DECLARE _nzdf varchar(500);
    SET _nzdf = 'CREATE UNIQUE INDEX nzdf ON employee (employee_id);';

IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.STATISTICS
               WHERE  table_name = 'employee'
               AND    index_name = 'nzdf') THEN
  EXECUTE _nzdf;
END IF;

DELIMITER;

Hi, Thank you for your assistance. Does this syntax prevent duplicate records or creates a uniqu index?

shammat · #9 (**permalink**) 12-04-08, 06:15

Quote:

Originally Posted by healdem

if the teacher is trying to teach pupils to use triggers to check for duplicates I'd agree, however if the teacher is using this a method of learning how to use triggers then I'd disagree.. it may well be a suitable way of expressign concepts and ideas behind triggers

But it's not a good example.
It is misleading and I'm sure one could come up with a better example to show the ideas behind a trigger rather than trying to implement something that does not make sense.

gvee · #10 (**permalink**) 12-04-08, 06:16

Yes, the code will prevent duplicate employee_id's from being created.

healdem · #11 (**permalink**) 12-04-08, 07:08

Quote:

Originally Posted by MaryH

Hi, Thank you for your assistance. Does this syntax prevent duplicate records or creates a uniqu index?

why don't you try it out, see if it does what is required
and if not make some attempt at doing your own homework

r937 · #12 (**permalink**) 12-04-08, 08:20

healdem, he did make an attempt, it is shown in post #1

healdem · #13 (**permalink**) 12-04-08, 08:37

Quote:

Originally Posted by r937

healdem, he did make an attempt, it is shown in post #1

I'd agree
except post #8 infers...
I can't be bothered to check what someone has given me, symptomatic of the 'my time is far more important than your time', that seems ooh so prevalent these days

in the time taken to make the post, and wait for a response the OP could have plugged in the code and made their own assessment of whether it works or not

this poster isn't the only on, and may be it reflects poorly on me, or on my current state of mind. I'm all in favour of being hesitant on live data, on processes that may change something irrecoverably. thats a good time to ask is this right.

..but not in this case

Pat Phelan · #14 (**permalink**) 12-04-08, 12:07

MaryH: To cut through all of the debate, the trigger as written will prevent the entry of more than one row with a given employee_id value.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech