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

Thread
New to TRIGGER and CALL. Example gives errors.Daevid Vincent10 Apr