List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 12 2006 2:59am
Subject:RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
View as plain text  
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

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