1

Here is what I want to do (a part of my trigger):

BEGIN
DECLARE updatecount INT default 0;
declare number int;
declare column varchar(10);
declare i int default 1;
declare j int default 1;
while i<=6 do
    set column = "num"+i; /*will be num1 num2, so on*/
    set number = NEW.@column; /* will represent NEW.num1, num2 so on*/

I know that the last 2 lines are not correct. But I wanted you guys to understand what I want to do. Can anyone tell me the correct syntax? thanks.

EDIT:

what I want to do with this trigger is when a new row added into the resultsTBL, I want to check all the rows in the guessesTBL to find how many of them are same.

For example we have 2 rows in guessesTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    5    10   15   20   26
 4    8    12   16   25   40

then we add a new row into the resultsTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    8    17   20   25   40

after that trigger will compare the new added row with all the rows in the guessesTBL:

for the 1st row the result is: 2
for the 2nd row the result is: 3

pseudocode:

CREATE DEFINER = CURRENT_USER TRIGGER `myDB`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
for each field in resultsTBL (num1, num2, ... num6)
  number= num1...num6
  for each field in guessesTBL (num1, num2, ... num6)
    if number == guessesTBL.num1,num2,...num6
      counter++
5
  • Can you at least post your pseudo code with your expected TRIGGER action? This could be possible with Dynamic SQL.
    – oNare
    Jul 20, 2015 at 18:54
  • @oNare I did add
    – Who Cares
    Jul 20, 2015 at 19:09
  • @oNare there is another field in the guessesTBL named howmany. I want to put the result into it. Sth like that: UPDATE guessesTBL set howmany = @counter
    – Who Cares
    Jul 20, 2015 at 19:24
  • This is impossible because Dynamic SQL is not allowed in stored function or trigger SQL Statement, so, you can't make this in a TRIGGER but I'll see if I can make it in a Stored Procedure inside the TRIGGER.
    – oNare
    Jul 20, 2015 at 20:08
  • @oNare dude, I am failed. I just have one question: Can we update tableX after insert a row into tableY? I want that "for each row" statement to work for the tableX not for the tableY?
    – Who Cares
    Jul 21, 2015 at 10:24

1 Answer 1

1

Your syntax is wrong. I don't think putting a loop inside a TRIGGER is a good idea, but I made you a Stored Procedure as example:

Procedure:

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN

SET @counter=0;
SET @column='';

WHILE (@counter < 6) DO
    SET @counter=@counter+1;
    set @column = CONCAT("num",@counter); /*will be num1 num2, so on*/
    SELECT CONCAT(" SET @number = NEW.",@column,"; ");

END WHILE;
END$$

DELIMITER ;

Output Test:

mysql> call test.new_procedure();
+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num1;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num2;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num3;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num4;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num5;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num6;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 
1
  • Thanks. But actually I didn't understand that. Could you please just correct my own code? Newbie is here :)
    – Who Cares
    Jul 20, 2015 at 19:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.