List:General Discussion« Previous MessageNext Message »
From:Gordon Date:April 12 2006 11:17am
Subject:RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
View as plain text  
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:daevid@stripped]
> Sent: Tuesday, April 11, 2006 10:00 PM
> To: mysql@stripped
> 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:sgreen_mysql@stripped]
> > Sent: Tuesday, April 11, 2006 12:41 PM
> > To: Daevid Vincent; mysql@stripped
> > Subject: Re: FW: New to TRIGGER and CALL. Example gives
> > errors. (repost)
> >
> >
> >
> > --- Daevid Vincent <daevid@stripped> 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/mysql?unsub=1

Thread
FW: New to TRIGGER and CALL. Example gives errors. (repost)Daevid Vincent11 Apr
Re: FW: New to TRIGGER and CALL. Example gives errors. (repost)Shawn Green11 Apr
  • RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)Daevid Vincent12 Apr
RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)Gordon12 Apr
RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)Shawn Green12 Apr