mysql insert update where

[iamnate]

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
-> BEGIN
-> 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);
-> END;
-> |

[james438]

What you are asking for help with is most likely beyond the expertise of the members here. You are asking about how to improve your trigger syntax, but I think what you should really be looking into is FOREIGN KEY Constraints (http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html). This will help you to maintain your referential integrity.

The odd thing is that I never had the need to use foreign keys. I ended up using php scripts that do the same thing.