List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:April 11 2006 7:41pm
Subject:Re: FW: New to TRIGGER and CALL. Example gives errors. (repost)
View as plain text  

--- 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

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
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