Reputation: 141
Based on my previous post found here im able to insert the values to the 2nd table when the status on first table changes, but it keeps adding indiscriminately, i need to check if the submit_id has already been inserted into the 2nd table and then update the fields not insert it gain, how would i do that check before the trigger is executed? Because the new.status and old.status refer to the row being edited not the row on table it's being inserted into, how can i compare that and insert or update if it already exists, Thanks
Upvotes: 0
Views: 2308
Reputation: 92845
You can use INSERT INTO ... ON DUPLICATE KEY UPDATE
syntax for that
If order for it to work properly you have to have a UNIQUE
constraint on submitId
column in your second table (let's call it students
).
ALTER TABLE students ADD UNIQUE (submitId);
Now an improved version of a trigger
DELIMITER $$
CREATE TRIGGER tg_au_submissions
AFTER UPDATE ON submissions
FOR EACH ROW
BEGIN
IF NEW.status = 1 THEN
INSERT INTO students (submitId, studentName, submitDate, contacts, email)
VALUES (NEW.submitId, NEW.studentName, NEW.submitDate, NEW.contacts, NEW.email)
ON DUPLICATE KEY UPDATE
studentName = VALUES(studentName),
submitDate = VALUES(submitDate),
contacts = VALUES(contacts),
email = VALUES(email);
END IF;
END$$
DELIMITER ;
Here is SQLFiddle demo
Upvotes: 1