Mysql Trigger Loop for 查询结果多行

Mysql Trigger Loop for query result with many rows

提问by elin3t

hi i have a database with many tables and foreign keys like this

嗨，我有一个包含许多表和外键的数据库，就像这样

CREATE TABLE IF NOT EXISTS `articulos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(63) NOT NULL,
  `contenido` text NOT NULL,
  `normas_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=138 ;

CREATE TABLE IF NOT EXISTS `aspectosambientales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(63) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;

CREATE TABLE IF NOT EXISTS `aspectosambientales_articulos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aspectosambientales_id` int(11) NOT NULL,
  `articulos_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aspaspectosambientales1`      (`aspectosambientales_id`),
  KEY `fk_aspee` (`articulos_id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 UTO_INCREMENT=225 ;

CREATE TABLE IF NOT EXISTS `empresas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `razonsocial` varchar(127) DEFAULT NULL,
  `nit` varchar(63) DEFAULT NULL,
  `direccion` varchar(127) DEFAULT NULL,
  `telefono` varchar(15) DEFAULT NULL,
  `web` varchar(63) DEFAULT NULL,
  `auth_user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `articulos_empresas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empresas_id` int(11) NOT NULL,
  `articulo_id` int(11) NOT NULL,
  `acciones` text,
  `responsable` varchar(255) DEFAULT NULL,
  `plazo` date DEFAULT NULL,
  `cumplido` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_normas_empresas_empresas1` (`empresas_id`),
  KEY `fk_normas_empresas_normas1` (`normas_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

and i need to create a trigger to fill the 'articulos_empresas' after insert in 'empresas' for all rows in 'articulos' that match with 'aspectosambientals' that the new 'empresas' selected.

并且我需要创建一个触发器来填充 'articulos_empresas' 后插入到 'empresas' 中所有与新 'empresas' 选择的 'aspectosambientals' 匹配的 'articulos' 中的行。

I get all 'articulos' with this query

我通过这个查询得到了所有的“articulos”

SELECT articulos_id FROM aspectosambientales_articulos 
    WHERE  aspectosambientales_id = ID 
        -- ID is the aspectosambientales_id selected when the 'empresas' row is created
        --  maybe something like NEW.aspectosambientales_id

but i dont know how create a loop like ' for loop' in trigger for every result in the query

但我不知道如何为查询中的每个结果在触发器中创建一个像“for 循环”这样的循环

some like this:

有些像这样：

CREATE TRIGGER 'filltableae' AFTER INSERT ON 'empresas' 
FOR EACH ROW
BEGIN 
DECLARE arrayresult = (SELECT articulos_id FROM aspectosambientales_articulos 
    WHERE  aspectosambientales_id = NEW.aspectosambientales_id)
--- here is when i have to do the loop for all the results
--- for ids in arrayresults
---  insert into articulos_empresas ('',NEW.id, ids, '', '' ,'','')
--- endfor
END

thanks!!!

谢谢！！！

采纳答案by Razvan

As far as I know you can iterate through the result of a SELECT query using cursors. See here : http://dev.mysql.com/doc/refman/5.0/en/cursors.html

据我所知，您可以使用游标遍历 SELECT 查询的结果。见这里：http: //dev.mysql.com/doc/refman/5.0/en/cursors.html

回答by elin3t

Based on @Razvan answer i left here the code for the trigger, so maybe can help somebody

基于@Razvan 的回答，我把触发器的代码留在这里，所以也许可以帮助某人

DROP TRIGGER IF EXISTS AEINST;
DELIMITER //
CREATE TRIGGER AEINST AFTER INSERT ON procesos_aspectos
FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE ids INT;
    DECLARE cur CURSOR FOR SELECT articulos_id FROM aspectosambientales_articulos WHERE aspectosambientales_id = NEW.aspectosambientales_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO ids;
            IF done THEN
                LEAVE ins_loop;
            END IF;
            INSERT INTO articulos_empresas VALUES (null,ids, NEW.empresas_id,null,null,null,null);
        END LOOP;
    CLOSE cur;
END; //
DELIMITER ;

thanks again!

再次感谢！