REMOVE the semicolon ";" from " END;//" SQLyog has a problem with all of the procedures, functions and triggers RE the DELIMITER syntax.
> -----Original Message----- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 11, 2006 10:00 PM > To: mysql@lists.mysql.com > Cc: 'Shawn Green' > Subject: RE: FW: New to TRIGGER and CALL. Example gives errors. (repost) > > I was using SQLYog 5.03 RC1. > > vmware ~ # mysql --version > mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline > 5.1 > > But just to sanity check. I ssh'd in and tried this at the mysql command > line utility: > > vmware ~ # mysql somedatabase > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 415 to server version: 5.0.19-log > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> delimiter // > mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys > -> FOR EACH ROW > -> BEGIN > -> IF NEW.skey < 1 THEN > -> > Display all 187 possibilities? (y or n) > -> EW.skey = 1; > -> ELSEIF NEW.skey > 9 THEN > -> > Display all 187 possibilities? (y or n) > -> EW.skey = 9; > -> END IF; > -> END;// > delimiter ; > ERROR 1064 (42000): 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 '.skey = 1; > ELSEIF NEW.skey > 9 THEN > EW.skey = 9; > END IF; > END' at line 5 > mysql> delimiter ; > mysql> > > > > -----Original Message----- > > From: Shawn Green [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, April 11, 2006 12:41 PM > > To: Daevid Vincent; mysql@lists.mysql.com > > Subject: Re: FW: New to TRIGGER and CALL. Example gives > > errors. (repost) > > > > > > > > --- Daevid Vincent <[EMAIL PROTECTED]> wrote: > > > > > This may have been lost, so I'm reposting hoping for a clue > > as to why > > > the > > > mySQL example onlie gives me errors... > > > -----Original Message----- > > > Sent: Sunday, April 09, 2006 7:41 PM > > > > > > I'm trying to follow the example in the manual to create a trigger: > > > http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html > > > > > > #DROP TRIGGER upd_check; > > > delimiter // > > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys > > > FOR EACH ROW > > > BEGIN > > > IF NEW.skey < 1 THEN > > > SET NEW.skey = 1; > > > ELSEIF NEW.skey > 9 THEN > > > SET NEW.skey = 9; > > > END IF; > > > END;// > > > delimiter ; > > > > > > All I'm trying to do is enforce that my starkeys.skey column is > > > always in > > > the range of 1 through 9. I was planning to start with this example > > > and work > > > my way up. Ideally it should check on UPDATE or INSERT. The manual > > > recommended: > > > > > > "It can be easier to define a stored procedure separately and then > > > invoke it > > > from the trigger using a simple CALL statement. This is also > > > advantageous if > > > you want to invoke the same routine from within several triggers." > > > > > > But I don't know how to do that yet. > > > > > > vmware public_html # mysql --version > > > mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using > > > readline 5.1 > > > > > > But I just get these errors: > > > > > > Error Code : 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 > > 'delimiter > > > // > > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys > > > FOR EACH ROW > > > BEG' at line 2 > > > (0 ms taken) > > > > > > Error Code : 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 'ELSEIF > > > NEW.skey > > > > 9 THEN > > > SET NEW.skey = 9' at line 1 > > > (0 ms taken) > > > > > > Error Code : 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 'END IF' > > > at line > > > 1 > > > (0 ms taken) > > > > > > Error Code : 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 'END' at > > > line 1 > > > (0 ms taken) > > > > > > Error Code : 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 '// > > > delimiter' at line 1 > > > (0 ms taken) > > > > > > > This looks suspiciously like an interface issue, not a coding issue. > > How are you delivering these commands to your MySQL server and is it > > v5.0 or newer? > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
