Launch Trigger and Routine on Insert

Question

Im attempting to have MySQL automatically insert data into another table after insert into one. I know to do this required Triggers and potentially Routines. I have a couple I've been trying to modify to do what I wish to accomplish but I appear to be hitting a dead end due to lack of experience, so help is greatly appreciated.

The table that has data inserted (db_tou_tracking):

 tou_tracking_ID  ICP_ID  tou_tracking_start  tou_tracking_units
 ----------------------------------------------------------------
      2             2        2013-03-01            10.77
      3             2        2013-03-01            11.00

There are a couple of other columns here, that separate out by time, but I'm interested by day, rather than time.

Table data should go into compounded. So as each of the above rows are inserted, it will either create a new row if the tou_tracking_start and ICP_ID do not exist, or update the existing row.

  tou_tracking_daily_ID  ICP_ID  tou_tracking_start  tou_tracking_units
 ------------------------------------------------------------------------------
             1             2        2013-03-01            21.77
             2             2        2013-03-02            25.36

Below is my Tigger (no errors when setup on MySQL, and it does appear to call when data is attempted to be inserted):

BEGIN 
DECLARE presentcount INT;
SET presentcount = (SELECT count(*) FROM db_tou_tracking_daily WHERE tou_tracking_daily_day = 
        (SELECT tou_tracking_start FROM db_tou_tracking WHERE ICP_ID = db_tou_tracking_daily.ICP_ID ORDER BY tou_tracking_ID DESC)
    );
    IF (presentcount = 0) THEN
        INSERT INTO db_tou_tracking_daily (ICP_ID, tou_tracking_daily_day, tou_tracking_start)
            SELECT NEW.ICP_ID, NEW.tou_tracking_start, NEW.tou_tracking_units, calculate_units(NEW.ICP_ID, NEW.tou_tracking_start);
    ELSE 
        UPDATE db_tou_tracking_daily SET tou_tracking_daily_units = calculate_units(NEW.ICP_ID, tou_tracking_daily_day)
            WHERE ICP_ID = NEW.ICP_ID AND tou_tracking_daily_day = NEW.tou_tracking_start;
    END IF;

END

and then the routine it calls to calculate units.

    CREATE DEFINER=`root`@`localhost` FUNCTION `calculate_units`(ICP_ID INT, tou_tracking_daily_day DATE) RETURNS float
BEGIN
    DECLARE units FLOAT;
    DECLARE last_time DATE;
    DECLARE last_watts INT;
    DECLARE this_time DATETIME;
    DECLARE this_watts INT;
    DECLARE loop_done INT;
    DECLARE curs CURSOR FOR 
        SELECT tou_tracking_timestart, tou_tracking_units FROM db_tou_tracking WHERE ICP_ID = ICP_ID AND tou_tracking_start = tou_tracking_daily_day ORDER BY tou_tracking_start DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done = 1;

        SET last_time = (SELECT max(tou_tracking_start) FROM db_tou_tracking WHERE ICP_ID = ICP_ID AND tou_tracking_start < tou_tracking_daily_day);
        SET last_watts = (SELECT tou_tracking_units FROM db_tou_tracking WHERE ICP_ID = ICP_ID AND tou_tracking_start = last_time);
        SET last_time = CAST(tou_tracking_start AS DATETIME);

        SET loop_done = 0;
        SET units = 0;

        OPEN curs;
        REPEAT
            FETCH curs INTO this_time, this_watts;

            IF last_watts IS NOT NULL THEN
                SET units = units + (last_watts + this_watts);
            END IF;
            SET last_watts = this_watts;
            SET last_time = this_time;
        UNTIL loop_done END REPEAT;
        CLOSE curs;
    END

The routine throws back an error on line 3 when I try to run the SQL to setup the routine, but I can't see anything obviously wrong, but I'm not exactly sure what I'd be looking for.

Any help with this is hugely appreciated and any pointers that can be given along the way. Thanks :)

can you provide "create table statements" and some sample data as "insert statements" and expected result in compounded? — Lennart - Slava Ukraini, May 12 '14 at 05:58

score 0 · Answer 1 · answered May 12 '14 at 00:45

Attempting to replicate your issue, I'm going to guess the error you get is probably because you're not using a DELIMITER.

Executing a similar function creation statement I get the same error, and a syntax parse suggests it's not expecting the delimiter ;.

The one that causes an error on line 3.

CREATE DEFINER = 'root'@'localhost' FUNCTION test_func(foo INT) RETURNS FLOAT
BEGIN
    DECLARE bar FLOAT;
    RETURN 1;
END

Fixing it using delimiters.

DELIMITER $$
CREATE DEFINER = 'root'@'localhost' FUNCTION test_func(foo INT) RETURNS FLOAT
BEGIN
    DECLARE bar FLOAT;
    RETURN 1;
END$$
DELIMITER ;

If this does not fix your problem, are you able to provide a self contained function that doesn't rely on any of your existing tables, that also produces the same error so it can be tested?

score 0 · Answer 2 · answered May 12 '14 at 13:32

create table t1 ( start date not null, units decimal(5,2) not null );

create table t2 ( start date not null, units decimal(5,2) not null );

delimiter //

create trigger trg1 after insert on t1 for each row begin update t2 set units = units + new.units where start = new.start; if ROW_COUNT() = 0 then insert into t2 (start, units) values (new.start, new.units); end if; end //

delimiter ; //

mysql> select * from t1; Empty set (0.01 sec)

mysql> select * from t2; Empty set (0.00 sec)

mysql> insert into t1 (start, units) values ('2014-01-01',100.02); Query OK, 1 row affected (0.01 sec)

mysql> select * from t1; +------------+--------+ | start | units | +------------+--------+ | 2014-01-01 | 100.02 | +------------+--------+ 1 row in set (0.00 sec)

mysql> select * from t2; +------------+--------+ | start | units | +------------+--------+ | 2014-01-01 | 100.02 | +------------+--------+ 1 row in set (0.00 sec)

mysql> insert into t1 (start, units) values ('2014-01-01',200.05); Query OK, 1 row affected (0.01 sec)

mysql> select * from t1; +------------+--------+ | start | units | +------------+--------+ | 2014-01-01 | 100.02 | | 2014-01-01 | 200.05 | +------------+--------+ 2 rows in set (0.01 sec)

mysql> select * from t2; +------------+--------+ | start | units | +------------+--------+ | 2014-01-01 | 300.07 | +------------+--------+ 1 row in set (0.01 sec)

Launch Trigger and Routine on Insert

2 Answers2