Advanced Search

Results 1 to 2 of 2

Thread: mysql insert update where

  1. #1
    Join Date
    Aug 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default mysql insert update where

    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;
    -> |

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    80
    Thanked 89 Times in 87 Posts

    Default

    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. 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.
    To choose the lesser of two evils is still to choose evil. My personal site

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
  •