Why is the trigger wrong in MySQL?

Question

I spent a lot of time on it, but I still can not resolve it.

For some reasons that I want prevent some same inserting to database. So I choose to use the trigger. That is,

    CREATE TRIGGER trigger_card_usage_insert_block 
    BEFORE INSERT ON annual_card_usage_log 
    FOR EACH ROW
    BEGIN

    DECLARE lasttime INT;    -- hold the last insert time

    SELECT
        usage_time INTO lasttime
    FROM
        annual_card_usage_log
    WHERE
        NEW.card_id = card_id
    AND NEW.mch_id = mch_id
    AND NEW.store_id = store_id
    ORDER BY
        usage_time DESC
    LIMIT 1;

    -- only insert when after 300 second
    IF(
        lasttime != NULL
        AND(lasttime + 300 < new.usagetime)
    ) THEN
        INSERT INTO annual_card_usage_log(
            card_id ,
            mch_id ,
            store_id ,
            usage_time
        )
    VALUES
        (
            NEW.card_id ,
            NEW.mch_id ,
            NEW.store_id ,
            NEW.usage_time
        );
    END
    IF;

    END$

This is the creating code. And below is the table,

CREATE TABLE `annual_card_usage_log` (
      `usage_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
      `card_id` int(10) unsigned NOT NULL COMMENT '',
      `mch_id` int(10) unsigned NOT NULL COMMENT '',
      `store_id` int(10) DEFAULT NULL,
      `usage_time` int(10) NOT NULL COMMENT '',
      PRIMARY KEY (`usage_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Sadly I can not create the trigger, it seems wrong. Can you tell me why? Which code is wrong.

I use the mysql client called Sequel Pro, and when I run the sql I got a error message "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 Execution stopped!" I don't now the reason.

@anatol I use the mysql client called Sequel Pro, and when I run the sql I got a error message "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 Execution stopped!" I don't now the reason. — blackdog, Mar 05 '17 at 08:50
And about your issue - it seems like endless loop. You fire your trigger in inserts that fires in trigger — anatol, Mar 05 '17 at 08:53
take a look at this this question - http://stackoverflow.com/questions/8504904/insert-into-same-table-trigger-mysql — anatol, Mar 05 '17 at 08:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137259/discussion-between-blackdog-and-anatol). — blackdog, Mar 05 '17 at 08:59
I think you should to do verification before insert, this can solve your problem — anatol, Mar 05 '17 at 09:06
@anatol I don't have the source code, and the bug is cause by one application wrote by others who I can not ask for. So I can not edit the code directly. And I don't has the private key, so I can not redirect the API request. Now the client ask me to fix it. So I only find the trigger can did that. — blackdog, Mar 05 '17 at 09:10
@anatol is that means with `PROCEDURE` the trigger can do anything including insert a same table? I'm trying the `procedure` — blackdog, Mar 05 '17 at 09:17
It means that you won't be able to insert into the same table from a trigger and means that you should find another solution — anatol, Mar 05 '17 at 09:28

score 1 · Answer 1 · answered Mar 05 '17 at 10:14

Thanks to @anatol I solve it. I changed the way. I set the value to a invalid data. Then create a job to delete the invalid data.

Trigger can not insert into a same table because it will cause endless loop, like @anatol point out.

    DELIMITER $$
    CREATE TRIGGER triger_card_usage_insert_block BEFORE INSERT ON annual_card_usage_log FOR EACH ROW
    BEGIN

    DECLARE lasttime INT(10) ;
    SET lasttime =(
        SELECT
            usage_time
        FROM
            annual_card_usage_log
        WHERE
            NEW.card_id = card_id
        AND NEW.mch_id = mch_id
        AND NEW.store_id = store_id
        ORDER BY
            usage_time DESC
        LIMIT 1
    ) ;
    IF (lasttime + 300 > new.usage_time) THEN
    SET new.new_card_id = new.card_id;
    SET new.card_id = 0 ;
    END
    IF ;
    END$$
    DELIMITER ;

Why is the trigger wrong in MySQL?

1 Answers1