MySQL Forums
Forum List  »  Newbie

triggers and stored procedures
Posted by: Suman Patro
Date: August 18, 2015 03:55AM

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.

Options: ReplyQuote


Subject
Written By
Posted
triggers and stored procedures
August 18, 2015 03:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.