I have a MySQL trigger in which I want to insert/update an ID into tabe 1, just as long as that ID doesn't exists in the ID column of table 1 or table 2. I'm currently using an INSERT WHERE statement, which I read is wrong.
What I'm ultimately going for here is referential integrity, I want the content and menu tables to match where thay have the same columns. I can provide a few other triggers for this and the structure of the content and menu tables if it would be helpful.
-> USE MP_CONTENT
-> DELIMITER |
-> CREATE TRIGGER ContentToMenu BEFORE INSERT/UPDATE ON MP_CONTENT.CONTENT
-> FOR EACH ROW
-> DECLARE highest;
-> SET @highestsort = (SELECT DISTINCT MAX(SORT) FROM MP_MENU.MENU);
-> INSERT INTO MP_MENU.MENU (ID, TITLE, AKA, PUBLISHED, LINK, SORT) VALUES(NEW.ID, NEW.TITLE, NEW.AKA, NEW.PUBLISHED, CONCAT("?", CAST(NEW.ID AS CHAR)), @highestsort+1) WHERE ID NOT IN (SELECT ID FROM MP_CONTENT.CONTENT.ID) AND ID NOT IN (SELECT ID FROM MP_MENU.MENU.ID);