MySql – can AFTER INSERT trigger delete the row which invoked it

MySQLmysql-error-1442sqltriggers

Let me put it in simplest words possible – is it possible to delete the row, which actually set On the trigger i.e. I have an AFTER INSERT ON <table2> trigger, the SQL in the trigger INSERT / UPDATE another <table1> (based on a WHERE), and finally tends to delete the entry/row in the (the row which basically fired the trigger).

Trigger SQL:

DELIMITER ||
DROP TRIGGER IF EXISTS trg_adddata_tmp ||
CREATE TRIGGER trg_adddata_tmp
AFTER INSERT ON adddata_tmp
FOR EACH ROW 
    BEGIN
        IF EXISTS (SELECT * FROM adddata WHERE data_id = new.data_id AND account_name = new.account_name) THEN
            UPDATE adddata SET data_id = new.data_id, account_name = new.account_name, user_name = new.user_name, first_name = new.first_name, last_name = new.last_name WHERE data_id = new.data_id AND account_name = new.account_name;
        ELSE
            INSERT INTO adddata (data_id, account_name, user_name, first_name, last_name)
            VALUES(new.data_id, new.account_name, new.user_name, new.first_name, new.last_name);
        END IF;
        DELETE FROM adddata_tmp WHERE id = new.id;
    END;
||

Without the DELETE (just above the END;) the trigger works fine – UPDATE if exist otherwise INSERT – with DELETE statement gives the following error:

Error Code: 1442
Can't update table 'adddata_tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

By the way, the error is pretty self-explanatory, but still wanted to make sure if this is possible – if not this way, may be some other way around i.e. I want the adddata_tmp table to be empty (or clean-ed up) all the time (on INSERT copies the data to main adddata table)

One idea, I have in mind is, to use an EVENT to clean-up the adddata_tmp based on some status field – which gets set as the last statement in the trigger (in place of DELETE).

Best Answer

No, you can't do this with trigger, here's what the documentation says:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If adddata_tmp table needs to be empty all the time then I would not write trigger at all. Instead, I would recommend moving adddata update logic in the script/service that tries to insert the data into adddata_tmp.

update

If we are doing bulk inserts and the data (in adddata_tmp table) is not utilised anywhere else then we can write a cron job to clean up the table (i.e. the one that executes let's say every 10 minutes). Also, TRUNCATE would be more efficient (than DELETE) in this case.

Related Topic