triggers and stored procedures
I am in a mid of error that says: subquery returns more than 1 row(error code 1242). The program has 2 primary tables records , records_1, change_bit(intermediary table), archieve (output table) , triggers setflag, setflag_1 on records, records_1 respectively.
The explanation of the code is as follows: The values of the primary tables are to be fetched onto the archieve table only when the fields of the tab les records, records_1 changes.
The code is as follows :
Table Records:
CREATE TABLE `records` (
`R_id` INT(11) NOT NULL AUTO_INCREMENT,
`time_stamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`tag1` INT(11) NULL DEFAULT NULL,
`tag2` INT(11) NULL DEFAULT NULL,
`tag3` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`R_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Table Records_1:
CREATE TABLE `records_1` (
`C_id` INT(11) NOT NULL AUTO_INCREMENT,
`time_stamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`tag4` INT(11) NULL DEFAULT NULL,
`tag5` INT(11) NULL DEFAULT NULL,
`tag6` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`C_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Table Change_bit:
CREATE TABLE `change_bit` (
`R_id` INT(11) NULL DEFAULT NULL,
`time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tag1` INT(11) NULL DEFAULT NULL,
`tag2` INT(11) NULL DEFAULT NULL,
`tag3` INT(11) NULL DEFAULT NULL,
`tag4` INT(11) NULL DEFAULT NULL,
`tag5` INT(11) NULL DEFAULT NULL,
`tag6` INT(11) NULL DEFAULT NULL,
`b` INT(11) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Trigger SetFlag:
CREATE DEFINER=`root`@`localhost` TRIGGER `setFlag` AFTER INSERT ON `records` FOR EACH ROW BEGIN
CALL SP1(new.R_id, new.time_stamp, 0);
END
Trigger setFlag_1:
CREATE DEFINER=`root`@`localhost` TRIGGER `setflag_1` AFTER INSERT ON `records_1` FOR EACH ROW BEGIN
CALL SP1(new.C_id, new.time_stamp, 1);
END
Stored Procedure SP1:
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP1`(IN `id` INT, IN `t` TIMESTAMP, IN `b` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
if b=0 THEN
IF id=1 THEN
INSERT INTO Change_bit(R_id, time_stamp,tag1, tag2, tag3,b) VALUES(id, t, 1, 1, 1, b);
ELSE
INSERT INTO Change_bit(R_id,time_stamp ,tag1, tag2, tag3,b)values (id, t,(SELECT (table1.tag1-table2.tag1) FROM Records table1 INNER JOIN Records table2 where (table1.R_id=id AND table2.R_id=id-1 and b=0)),(SELECT (table1.tag2-table2.tag2) FROM Records table1 INNER JOIN Records table2 where (table1.R_id=id AND table2.R_id=id-1 and b=0)),(SELECT (table1.tag3-table2.tag3) FROM Records table1 INNER JOIN Records table2 where (table1.R_id=id AND table2.R_id=id-1 and b=0)),b ) ;
END IF;
IF( select tag1 from Change_bit where R_id = id and b=0)<>0
Then insert into ARCHIEVE (R_id, time_stamp, tag, datavalues) values(id,t ,'tag1' ,(select tag1 from Records where R_id =id) );
END IF;
IF( select tag2 from Change_bit where R_id = id and b=0)<>0
Then insert into ARCHIEVE(R_id, time_stamp, tag, datavalues) values(id,t ,'tag2' ,(select tag2 from Records where R_id =id) );
END IF;
IF( select tag3 from Change_bit where R_id = id and b=0)<>0
Then insert into ARCHIEVE(R_id, time_stamp, tag, datavalues) values(id,t ,'tag3' ,(select tag3 from Records where R_id =id) );
END IF;
END IF;
IF b=1 THEN
IF id=1 THEN
INSERT INTO Change_bit(R_id, time_stamp,tag4, tag5, tag6, b) VALUES(id, t, 1, 1, 1, 1);
ELSE
INSERT INTO Change_bit(R_id,time_stamp ,tag4, tag5, tag6,b)values (id, t,(SELECT (table3.tag4-table4.tag4) FROM Records_1 table3 INNER JOIN Records_1 table4 where (table3.C_id=id AND table4.C_id=id-1 and b=1)),(SELECT (table3.tag5-table4.tag5) FROM Records_1 table3 INNER JOIN Records_1 table4 where (table3.C_id=id AND table4.C_id=id-1 and b=1)),(SELECT (table3.tag6-table4.tag6) FROM Records_1 table3 INNER JOIN Records_1 table4 where (table3.C_id=id AND table4.C_id=id-1 and b=1)),b ) ;
END IF;
IF( select tag4 from Change_bit where R_id = id and b=1)<>0
Then insert into ARCHIEVE (R_id, time_stamp, tag, datavalues) values(id,t ,'tag4' ,(select tag4 from Records_1 where C_id =id) );
END IF;
IF( select tag5 from Change_bit where R_id = id and b=1)<>0
Then insert into ARCHIEVE(R_id, time_stamp, tag, datavalues) values(id,t ,'tag5' ,(select tag5 from Records_1 where C_id =id) );
END IF;
IF( select tag6 from Change_bit where R_id = id and b=1)<>0
Then insert into ARCHIEVE(R_id, time_stamp, tag, datavalues) values(id,t ,'tag6' ,(select tag6 from Records_1 where C_id =id) );
END IF;
END IF:
END
Output Table Archieve:
CREATE TABLE `archieve` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`R_id` INT(11) NOT NULL,
`time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tag` VARCHAR(50) NOT NULL,
`datavalues` INT(11) NOT NULL,
PRIMARY KEY (`Id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Kindly provide some resolutions for the same.