MySQL Trigger/Stored Procedure – Update Another Table Row by Row

MySQLphpmyadminstored-procedurestriggerupdate

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.

this will be a little long, be patient please.

First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.

Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).

What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.

Structure of lotteryTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew 
1       5    6     8    15      18    25     08-21-2015         0
2       7    15    18   30      40    45     08-21-2015         0

Structure of resultsTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date  
1      15    18    35    38    40    47     08-21-2015

Now, here is the pseudocode of what i want:

after insert a new row into resultsTBL
   total <- 0
   for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date
       total <- calculate how many of them are the same with the resultsTBL's row.
   update lotteryTBL set howmanyknew = total where "some condition"

First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.

Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?

Best Answer

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table.

Table lotteryTBL and resultsTBL:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           0 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           0 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.resultsTBL;
Empty set (0.00 sec)

mysql>

Inserting on resultsTBL:

mysql> INSERT INTO test.resultsTBL(id, num1, num2, num3, num4, num5, num6)VALUES('1', '1', '5', '4', '8', '25', '40');
Query OK, 1 row affected (0.00 sec)

mysql>

RESULT:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           2 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           4 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql>

If you look in the INSERT:

  • There are 2 values matching for the ID = 1: 1,5.
  • There are 4 values matching for the ID = 2: 4,8,25,40.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.resultsTBL_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
BEGIN
    CALL test.sp_lottery(NEW.id,NEW.num1,NEW.num2,NEW.num3,NEW.num4,NEW.num5,NEW.num6);
END$$
DELIMITER ;

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lottery`(
IN eID INT(5),
IN eBLAH01 INT(5),
IN eBLAH02 INT(5),
IN eBLAH03 INT(5),
IN eBLAH04 INT(5),
IN eBLAH05 INT(5),
IN eBLAH06 INT(5))
BEGIN
    # [ lotteryTBL ]
    SET @ID=1;
    # [ ITERATE FOR EVERY ROW IN lotteryTBL ]
    WHILE @ID IS NOT NULL DO
        SET @NUM1=(SELECT num1 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM2=(SELECT num2 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM3=(SELECT num3 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM4=(SELECT num4 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM5=(SELECT num5 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM6=(SELECT num6 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @howmanyknew=0;
        # [ NUM1 ]
        IF (eBLAH01=@NUM1 || eBLAH02=@NUM1 || eBLAH03=@NUM1 || eBLAH04=@NUM1 || eBLAH05=@NUM1 || eBLAH06=@NUM1 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
         # [ NUM2 ]
        IF (eBLAH01=@NUM2 || eBLAH02=@NUM2 || eBLAH03=@NUM2 || eBLAH04=@NUM2 || eBLAH05=@NUM2 || eBLAH06=@NUM2 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM3 ]
        IF (eBLAH01=@NUM3 || eBLAH02=@NUM3 || eBLAH03=@NUM3 || eBLAH04=@NUM3 || eBLAH05=@NUM3 || eBLAH06=@NUM3 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM4 ]
        IF (eBLAH01=@NUM4 || eBLAH02=@NUM4 || eBLAH03=@NUM4 || eBLAH04=@NUM4 || eBLAH05=@NUM4 || eBLAH06=@NUM4 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM5 ]
        IF (eBLAH01=@NUM5 || eBLAH02=@NUM5 || eBLAH03=@NUM5 || eBLAH04=@NUM5 || eBLAH05=@NUM5 || eBLAH06=@NUM5 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM6 ]
        IF (eBLAH01=@NUM6 || eBLAH02=@NUM6 || eBLAH03=@NUM6 || eBLAH04=@NUM6 || eBLAH05=@NUM6 || eBLAH06=@NUM6 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ UPDATE ]
        IF @howmanyknew>0 THEN
            UPDATE test.lotteryTBL SET howmanyknew=@howmanyknew WHERE ID=@ID;
        END IF;
    SET @ID=(SELECT ID FROM test.lotteryTBL AS lt WHERE lt.ID>@ID ORDER BY ID ASC LIMIT 0,1);
    END WHILE;
END
Related Question