Triggers and stored proc in MySQL

Question

I used MSSQL stored procedures and triggers for a while; MySQL is getting me absolutely crazy about how to write even the simpler procedure.

Why I get a syntax error in this so stuoid trigger?

CREATE TRIGGER set_prio_default BEFORE INSERT ON categories
FOR EACH ROW
BEGIN
  set @my_prio := 1;
    SET new.prio := @my_prio;
END

In facts this TRIGGER is an oversemplification of:

DELIMITER $$
DROP PROCEDURE IF EXISTS `slot08`.`test` $$
CREATE PROCEDURE `slot08`.`test` ()
BEGIN
     select 1 + max(prio) from categories INTO @my_prio;
     select @my_prio;
END $$
DELIMITER ;

Still i do not understand how to use variables in procedures. If I use a DECLARE statement and the variable name miss the @ character I got an error from mysql telling me "unknown system variable" - but many examples I saw used this syntax

I mean:

this does not work

CREATE TRIGGER set_prio_default BEFORE INSERT ON categories
FOR EACH ROW
BEGIN
  declare  my_prio integer default 1;
  set my_prio := 1;
    SET new.prio := my_prio;
END

If I use @ I get syntax error.

Have some hints?

Thanks!

Answer 1

I dont think you have to use the := operator. A simple equals will do. All variables declared inside the stored procedure must be prefixed with the @symbol.

Answer 2

Hey, found the answer. Hope that people with so little experience as me in MySQL procedures could avoid to spend the time I have spent on the same issue. This does work:

DELIMITER $$
CREATE TRIGGER blablabla    
BEFORE INSERT ON myStupidTable
FOR EACH ROW
BEGIN
      declare  my_prio integer;
      select   1 + max(prio) from myStupidTable INTO my_prio;
      SET      new.prio := my_prio;
END $$
DELIMITER ;

It seems that the MySQL syntax errors experienced so far were a delimiter issue.

Greetings

Daniel