tQbLxcBG

1CREATE TABLE IF NOT EXISTS `articulos` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `nombre` varchar(63) NOT NULL,
4  `contenido` text NOT NULL,
5  `normas_id` int(11) NOT NULL,
6  PRIMARY KEY (`id`)
7) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=138 ;
8
9CREATE TABLE IF NOT EXISTS `aspectosambientales` (
10  `id` int(11) NOT NULL AUTO_INCREMENT,
11  `nombre` varchar(63) NOT NULL,
12  PRIMARY KEY (`id`)
13) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;
14
15CREATE TABLE IF NOT EXISTS `aspectosambientales_articulos` (
16  `id` int(11) NOT NULL AUTO_INCREMENT,
17  `aspectosambientales_id` int(11) NOT NULL,
18  `articulos_id` int(11) NOT NULL,
19  PRIMARY KEY (`id`),
20  KEY `fk_aspaspectosambientales1`      (`aspectosambientales_id`),
21  KEY `fk_aspee` (`articulos_id`) 
22) ENGINE=InnoDB  DEFAULT CHARSET=utf8 UTO_INCREMENT=225 ;
23
24CREATE TABLE IF NOT EXISTS `empresas` (
25  `id` int(11) NOT NULL AUTO_INCREMENT,
26  `razonsocial` varchar(127) DEFAULT NULL,
27  `nit` varchar(63) DEFAULT NULL,
28  `direccion` varchar(127) DEFAULT NULL,
29  `telefono` varchar(15) DEFAULT NULL,
30  `web` varchar(63) DEFAULT NULL,
31  `auth_user_id` int(11) NOT NULL,
32  PRIMARY KEY (`id`)
33) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
34
35CREATE TABLE IF NOT EXISTS `articulos_empresas` (
36  `id` int(11) NOT NULL AUTO_INCREMENT,
37  `empresas_id` int(11) NOT NULL,
38  `articulo_id` int(11) NOT NULL,
39  `acciones` text,
40  `responsable` varchar(255) DEFAULT NULL,
41  `plazo` date DEFAULT NULL,
42  `cumplido` tinyint(1) DEFAULT NULL,
43  PRIMARY KEY (`id`),
44  KEY `fk_normas_empresas_empresas1` (`empresas_id`),
45  KEY `fk_normas_empresas_normas1` (`normas_id`)
46) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
47	
48SELECT articulos_id FROM aspectosambientales_articulos 
49    WHERE  aspectosambientales_id = ID 
50        -- ID is the aspectosambientales_id selected when the 'empresas' row is created
51        --  maybe something like NEW.aspectosambientales_id
52	
53CREATE TRIGGER 'filltableae' AFTER INSERT ON 'empresas' 
54FOR EACH ROW
55BEGIN 
56DECLARE arrayresult = (SELECT articulos_id FROM aspectosambientales_articulos 
57    WHERE  aspectosambientales_id = NEW.aspectosambientales_id)
58--- here is when i have to do the loop for all the results
59--- for ids in arrayresults
60---  insert into articulos_empresas ('',NEW.id, ids, '', '' ,'','')
61--- endfor
62END
63	
64DROP TRIGGER IF EXISTS AEINST;
65DELIMITER //
66CREATE TRIGGER AEINST AFTER INSERT ON procesos_aspectos
67FOR EACH ROW
68BEGIN
69    DECLARE done INT DEFAULT FALSE;
70    DECLARE ids INT;
71    DECLARE cur CURSOR FOR SELECT articulos_id FROM aspectosambientales_articulos WHERE aspectosambientales_id = NEW.aspectosambientales_id;
72    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
73
74    OPEN cur;
75        ins_loop: LOOP
76            FETCH cur INTO ids;
77            IF done THEN
78                LEAVE ins_loop;
79            END IF;
80            INSERT INTO articulos_empresas VALUES (null,ids, NEW.empresas_id,null,null,null,null);
81        END LOOP;
82    CLOSE cur;
83END; //
84DELIMITER ;