MySQL creating simple trigger issue (noob in MySQL)

Question

I need to create a simple trigger:

DELIMITER //
CREATE TRIGGER `create_stock_articulo` AFTER INSERT ON `almacen_tb_articulos`
 FOR EACH ROW
    BEGIN
        DECLARE cur CURSOR FOR SELECT id FROM 'almacen_tb_almacenes';
        DECLARE id INT;
        OPEN cur;
        REPEAT
        FETCH cur INTO id;
             INSERT INTO 'almacen_tb_stock' VALUES (id,NEW.id);
        UNTIL done END REPEAT;
        CLOSE cur;
    END;//

This trigger is supposed to add rows on the table 'almacen_tb_stock' when a row is inserted in 'almacen_tb_articulos'. Each added row must contain 'almacen_tb_almacenes.id' and the new 'almacen_tb_articulos.id'.

When I try to create this trigger I get this error:

#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 ''almacen_tb_almacenes'; DECLARE id INT; OPEN cur; REPEAT FETCH cur I' at line 4

I can't see where the error is.

The table definitions are these:

almacen_tb_articulos:

CREATE TABLE IF NOT EXISTS `almacen_tb_articulos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `codigo` varchar(15) NOT NULL,
  `descripcion` varchar(150) NOT NULL,
  ... more irrelevant fields,
  PRIMARY KEY (`id`),
  KEY `id_familia` (`id_familia`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=43

almacen_tb_almacenes:

CREATE TABLE IF NOT EXISTS `almacen_tb_almacenes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `almacen` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `bloqueado` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

almacen_tb_stock:

CREATE TABLE IF NOT EXISTS `almacen_tb_stock` (
  `id_almacen` int(11) NOT NULL,
  `id_articulo` int(11) NOT NULL,
  `cantidad` int(11) NOT NULL,
  PRIMARY KEY (`id_almacen`,`id_articulo`),
  KEY `id_almacen` (`id_almacen`),
  KEY `id_articulo` (`id_articulo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT id FROM 'almacen_tb_almacenes' or SELECT id FROM almacen_tb_almacenes? — Álvaro González
– Álvaro González, Commented Feb 7, 2014 at 13:20

peterm · Accepted Answer · 2014-02-09 06:55:28Z

1

If you don't have a particular reason for using a cursor you can just use INSERT INTO ... SELECT FROM syntax which will greatly simplify your code

CREATE TRIGGER create_stock_articulo
AFTER INSERT ON almacen_tb_articulos
FOR EACH ROW
  INSERT INTO almacen_tb_stock (id_almacen, id_articulo) -- you have to add candidad here
  SELECT id, NEW.id -- and a value for candidas too
    FROM almacen_tb_almacenes;

Note: In your almacen_tb_stock table you have a third NOT NULL column cantidad for which you have to provide a value in your insert statement, or define DEFAULT value in your schema.

answered Feb 9, 2014 at 6:55

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Burch · Accepted Answer · 2014-02-07 13:48:29Z

0

I see several problems with your code:

1) The table name is enclosed in single quotes, 'almacen_tb_almacenes', which mean that it should be interpreted as a string literal. Instead enclose it with like so: almacen_tb_almacenes. Or don't bother, because it can stand without the quotes.

2) Your code is missing a couple of declarations related to the cursor:

declare done int default false;
declare continue handler for not found set done = true;

3) Your loop structure is not quite right. Try this instead:

open cur;
read_loop: loop
    fetch cur into id;
    if done then
        leave read_loop;
    end if;
    insert into almacen_tb_stock VALUES (id,NEW.id);
end loop;
close cur;

answered Feb 7, 2014 at 13:48

Tim Burch

1,0937 silver badges9 bronze badges

1 Comment

mbecares Over a year ago

Thanks for the response, on Monday I'll test it but it looks pretty good.

Collectives™ on Stack Overflow

MySQL creating simple trigger issue (noob in MySQL)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related