MySQL触发器循环查询结果多行

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

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 ;

并且我需要创建一个触发器，以在“empresas”中插入与新“empresas”选择的“aspectosambientals”相匹配的“articulos”中的所有行后，在“empresas”中填充“articulos_empresas”。

我用此查询得到所有“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

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

一些像这样:

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

谢谢!!!

最佳答案

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