Results 1 to 4 of 4
Dec 3, 2008, 17:31 #1
- Join Date
- Dec 2008
- 0 Post(s)
- 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:
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;
Any help would be appreciated!