Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Unanswered: Trigger for preventing duplicates (Please Help)

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maryh, you must have a twin brother, larry s, in the same course --

    http://www.sitepoint.com/forums/showthread.php?t=587005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shammat, it's a homework assignment, that's why it has to be a trigger
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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;
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2008
    Posts
    13
    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?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes, the code will prevent duplicate employee_id's from being created.
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, he did make an attempt, it is shown in post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •