SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trigger for Duplicate rows (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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this is a homework assignment that has been posted on several other forums

    the correct answer in the real world is: use a unique constraint

    sorry that you don't want a unique constraint, but a trigger is wrong here

    and wasn't this assignment actually due two months ago? that's when you first started playing around with triggers, except it was with dogs and places, not employees and places

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, The teachers gave me an extension for this assign. I know that a unique constraint is the right answer. But the teachers have asked for a Trigger.


    Can you be kind enough by just telling me where there is a probem with the code I provided?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't have enough information (you did not reveal what the syntax error was)

    and i'm not sure i want to

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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