List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:April 12 2006 2:22pm
Subject:RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
View as plain text  
I don't have a 5.0+ server to test with right now but this should work

--- Daevid Vincent <daevid@stripped> wrote:

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

You need a SET command here. That's how MySQL's SQL does assignments.
That's also the wrong name, you need the table name of NEW not EW

SET NEW.skey = 1;

>     -> ELSEIF NEW.skey > 9 THEN
>     -> 
> Display all 187 possibilities? (y or n)
>     -> EW.skey = 9;

Same comment here:
SET NEW.skey = 9

>     -> END IF;
>     -> END;//

(Same comment as from other thread.) No ; after the END or END IF
statements
http://dev.mysql.com/doc/refman/5.0/en/begin-end.html
http://dev.mysql.com/doc/refman/5.0/en/flow-control-constructs.html

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

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