MySQL Trigger promlem

by lisag
7 replies
I'm pulling my hair out over this. Anyone have any ideas?

Code:
DELIMITER $$
CREATE TRIGGER `update_features` AFTER INSERT ON `jos_cbsubs_plans` 
BEGIN
DECLARE itype varchar(16); 
SET itype := 'usersubscription';
IF NEW.item_type =  itype THEN  INSERT INTO jos_cbsubs_dya_subscription_features (subs_id,published) VALUES (NEW.id,NEW.published);
END IF; 
END $$
DELIMITER ;
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN
DECLARE itype varchar(16);
SET itype := 'usersubscription';
IF NEW.ite' at line 2
#mysql #promlem #trigger
  • Profile picture of the author Steve Diamond
    Hi, Lisa. Have you tried it without the variable declaration? You don't really need it, and I've found some posts here and there that report the same error when trying to declare variables inside a trigger. Not sure why, but in your case this is simpler anyway, isn't it?

    Code:
    DELIMITER $$
    CREATE TRIGGER `update_features` AFTER INSERT ON `jos_cbsubs_plans` 
    BEGIN
    IF NEW.item_type =  'usersubscription' THEN  INSERT INTO jos_cbsubs_dya_subscription_features (subs_id,published) VALUES (NEW.id,NEW.published);
    END IF; 
    END $$
    DELIMITER ;
    HTH.

    Steve
    Signature
    Mindfulness training & coaching online
    Reduce stress | Stay focused | Keep positive and balanced
    {{ DiscussionBoard.errors[1122669].message }}
    • Profile picture of the author lisag
      "IF NEW.item_type = 'usersubscription' "

      I tried that initially and it failed as well. I just posted it to ScriptLance and someone is fixing it for $8. Can't beat that. Thanks for your response.

      Lisa
      Signature

      -- Lisa G

      {{ DiscussionBoard.errors[1122810].message }}
      • Profile picture of the author Steve Diamond
        I'd say $8 is a bargain considering the time you've spent. Now that you've whetted my curiosity, will you post the solution when you get it?

        Thanks.

        Steve
        Signature
        Mindfulness training & coaching online
        Reduce stress | Stay focused | Keep positive and balanced
        {{ DiscussionBoard.errors[1124020].message }}
        • Profile picture of the author lisag
          Code:
          DELIMITER $$
          CREATE TRIGGER `update_features` AFTER INSERT ON `jos_cbsubs_plans` 
          FOR EACH ROW BEGIN
          DECLARE itype varchar(16); 
          SET itype := 'usersubscription';
          IF NEW.item_type =  itype THEN  INSERT INTO jos_cbsubs_dya_subscription_features (subs_id,published) VALUES (NEW.id,NEW.published);
          END IF; 
          END $$
          DELIMITER ;
          See if you can spot the differences ;-)
          Signature

          -- Lisa G

          {{ DiscussionBoard.errors[1126178].message }}
          • Profile picture of the author Steve Diamond
            The only difference I see is FOR EACH ROW. Am I missing something else?

            Steve
            Signature
            Mindfulness training & coaching online
            Reduce stress | Stay focused | Keep positive and balanced
            {{ DiscussionBoard.errors[1127596].message }}
            • Profile picture of the author lisag
              Originally Posted by Steve Diamond View Post

              The only difference I see is FOR EACH ROW. Am I missing something else?

              Steve
              That was it. It doesn't even make sense to have a FOR EACH ROW statement on an after insert command since insert is a single process, but there you go....

              Something else interesting; after installing the trigger, the IF clause failed intermittently under testing depending upon the value that was passed. So I changed = to LIKE and now it works flawlessly. Go figure.
              Signature

              -- Lisa G

              {{ DiscussionBoard.errors[1127812].message }}
  • Profile picture of the author Kirk Ward
    I know diddley about coding, but I was able to follow some of this, and I have to agree with Lisa. Go figure.
    Signature
    "We are not here to sell a parcel of boilers and vats, but the potentiality of growing rich beyond the dreams of avarice."

    Dr. Samuel Johnson (Presiding at the sale of Thrales brewery, London, 1781)
    {{ DiscussionBoard.errors[1128832].message }}

Trending Topics