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]
