Delete the same row inserted in a table by trigger

Question

I'm trying to remove a row after inserted in a temp table and I get this error:

INSERT INTO `temp_program_counter` (`id`, `program_id`) values (NULL, '275')

Can't update table 'temp_program_counter' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

And here is my trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS `testtemp`$$

CREATE

    TRIGGER `testtemp` AFTER INSERT ON `temp_program_counter` 
    FOR EACH ROW BEGIN
        UPDATE `program` SET `view_count`=`view_count`+1;
        DELETE  FROM `temp_program_counter` WHERE id=new.id;
    END;
$$

DELIMITER ;

this table is a temp table that get's data with delay and after receiving must update the main table and be deleted from temp. thanks for helping in advance.

Answer 1

You probably have already resolved this in some way but I'll post the answer anyway:

You cannot refer to the same table that triggered the trigger in a trigger (or a combination of trigger and stored procedure). This could mean an infinite loop, so sql prevents it by giving that error, resulting in always the same error.

Answer 2

Try BEFORE INSERT Because, you are trying to delete a record which has just been inserted but may be not yet committed in this session. Hence you are getting the error.

If you use BEFORE INSERT, then all other record in the table with id=new.id would be deleted and the new record will be inserted successfully.

DELIMITER $$
DROP TRIGGER IF EXISTS `testtemp`$$

CREATE

    TRIGGER `testtemp` BEFORE INSERT ON `temp_program_counter` 
    FOR EACH ROW BEGIN
        UPDATE `program` SET `view_count`=`view_count`+1;
        DELETE  FROM `temp_program_counter` WHERE id=new.id;
    END;
$$

DELIMITER ;