#1172 - Result consisted of more than one row

[Clins]

I have three tables namely ozekimessagein,ozekimessageout and troubleshooter. I want to compare the values in tables ozekimessagein & troubleshooter and then insert in to the table ozekimessageout.

If i insert the first record in to the table ozekimessagein, the trigger execute quite well but when i insert the second record, the trigger displays the following message
" MySQL said:

#1172 - Result consisted of more than one row"

Please assist me how to get out of this error because i will be posting alot of records in the table ozekimessagein

DELIMITER |
CREATE TRIGGER troubleshoot_trig AFTER INSERT ON ozekimessagein
FOR EACH ROW

BEGIN
DECLARE soln_msg varchar(160);
DECLARE phone_number varchar(13);
DECLARE prob_code varchar(5);

SELECT sender INTO phone_number FROM ozekimessagein WHERE sender = NEW.sender;
SELECT msg INTO prob_code FROM ozekimessagein WHERE msg = NEW.msg;
SELECT msg INTO soln_msg FROM troubleshooter WHERE code = prob_code;

INSERT INTO ozekimessageout(receiver, msg) VALUES(phone_number, soln_msg);
END
|
DELIMITER;

 

[DonQuichote]

SELECT sender INTO phone_number  FROM ozekimessagein WHERE sender = NEW.sender;

Statements like this (SELECT ... INTO ...) give such messages if the WHERE clause selects more than 1 row. If this is intentional, you can limit the query:

SELECT sender INTO phone_number  FROM ozekimessagein WHERE sender = NEW.sender LIMIT 1;

But usually it uncovers a missing uniqueness constraint. You can use the ALTER IGNORE TABLE syntax to add the constraint while silently deleting all the non-unique rows.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[tsuji]

[0] There are a couple problems in the design of the tables and the trigger, from the perspective of the real [1] and of the specific mysql syntactical limitation(?) [2].

[1.1] From the perspective of the real, I am sure there should be no possible restriction like UNIQUE on the sender and msg as the same user (sender) can send at different time some (same or different) error code (msg). Hence there is inherently distinct possibility that these two lines will return more than one row.

>SELECT sender INTO phone_number FROM ozekimessagein WHERE sender = NEW.sender;
>SELECT msg INTO prob_code FROM ozekimessagein WHERE msg = NEW.msg;

The easy and correct way to do against all oddities is simply this.
[tt]
SELECT [red]NEW.[/red]sender INTO phone_number;
SELECT [red]NEW.[/red]msg INTO prob_code;
[/tt]
[1.2] And then the troubleshoot table, you should have it done correctly by imposing some sort of UNIQUE on the error code (code). I suppose you do have it one way or another. If not, you should have it done. In that case, the line should have no problem.

>[tt]SELECT msg INTO soln_msg FROM troubleshooter WHERE code = prob_code;[/tt]

and can be kept it that way.

[2] Now the problem of imposing LIMIT 1 on those three lines: it seems mysql has some limitation (?) of doing so in the trigger/stored procedure(?). It just does not seem to like it and will report 1172 as if it does not detect LIMIT 1. There seems to be some bugs on the use of LIMIT reported and corrected in the successive release. To be used in trigger, it seems still be restricted - I am not sure, maybe some keen-followers of mysql development can shed more light on it.

 

[DonQuichote]

Looking at the original post, I don't think you wrote what you meant. My guess is that you just want to store the sender and the code into the variables. You can do this with the short SELECT statements that tsuji gave you or with SET statements. But the whole thing could just be done in one statement:

INSERT INTO ozekimessageout(receiver, msg)
      SELECT NEW.sender, msg  FROM troubleshooter WHERE code = NEW.msg;

This way, every row is inserted into the messageout table, even for more than one corresponding row in the troubleshooter table.

Note that your first two select statements do nothing more than keep the same value unless the value does not exist. So what is what the trigger is supposed to do? Is it supposed to check the existence or just to fill the variables? What do you expect from it?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[tsuji]

I think what I was getting at too, as a syntactical problem of general setting, is the use of LIMIT inside trigger. The rest I have nothing more to add without knowing the full scope of functionality of the trigger the op intended to put into it.