MySql Triggers

[whizzle]

I'm trying to create a MySql trigger which does something like this:

// sudo code, doesn't work....
CREATE TRIGGER trigger1 BEFORE INSERT ON Category
BEGIN
UPDATE Category SET order = OLD.order + 1 WHERE order > NEW.order
END

 

However, phpmyadmin keeps telling me i have an error in my syntax (which I think it's lying).

 

server stats:

xampp

Server: Localhost via UNIX socket

Server version: 5.1.44

Protocol version: 10

User: root@localhost

MySQL charset: UTF-8 Unicode (utf8)

Apache/2.2.14 (Unix) DAV/2 mod_ssl/2.2.14 OpenSSL/0.9.8l PHP/5.3.1 mod_perl/2.0.4 Perl/v5.10.1

MySQL client version: 5.1.44

PHP extension: mysql

phpmyadmin Version information: 3.2.4

[trq]

What is the exact error and code?

[Mchl]

It sure is not lying. You do have error in your code. For one, you haven't got a command delimiter at the end of UPDATE .. .query

 

Try like this

CREATE TRIGGER trigger1 BEFORE INSERT ON Category
BEGIN
UPDATE Category SET order = OLD.order + 1 WHERE order > NEW.order;
END||

 

and set up a delimiter to || in the textbox below SQL window in phpmyadmin

[mikosiko]

your trigger is wrong because you are trying to alter the same table upon the trigger is declared... that is not allowed

 

"A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

 

read http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

[whizzle]

thanks so far gang! The first error was "order" is a reserved keyword in SQL so it had to be wrapped in backticks so I just changed it to orderId

 

however, I still have a syntax error

CREATE TRIGGER trigger1 BEFORE INSERT ON Category
BEGIN
UPDATE Category SET orderId=OLD.orderId + 1 WHERE orderId > 3;
END||

#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 'BEGIN UPDATE Category SET orderId=OLD.orderId + 1 WHERE orderId > 3; END' at line 2

 

Thanks  mikosiko, I'll worry about the table thing as soo as i get some syntax happening.

p.s. if you guys want to merge this to the other  trigger thread that is fine by me. but they are slightly different or could become more so.

[mikosiko]

 

Thanks  mikosiko, I'll worry about the table thing as soo as i get some syntax happening.

p.s. if you guys want to merge this to the other  trigger thread that is fine by me. but they are slightly different or could become more so.

 

    is a not a syntax problem what you have there... just doesn't work in that way.

 

"A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

 

[whizzle]

ok. le'ts "close" this thread as it appears to be a duplicate now. I'll lurk in the other one.

 

Thanks a ton guys.