Mysql triggers on table causing #1442 error

45 Views Asked by At

In order to create a multilanguage website, I have a MySQL database and a table "languages" with id (INT), name (VARCHAR), lang (CHAR(2)) and ts_update (TIMESTAMP) fields, where I store every string with the appropriate language. The ts_update field is auto updated.

In this same table, I have a few special records named GLOBAL_UPDATE_TS, for each available language. I want to use these records to check as fast as possible if a given language has been updated since the last time it was checked during a user's session, and I thought it would be better to check one specific field and do something like :

SELECT UNIX_TIMESTAMP(languages.ts_update)
FROM languages
WHERE languages.lang = 'EN'
AND languages.name = 'GLOBAL_UPDATE_TS'"

than to directly get all potentially the updated keys and do something like :

SELECT languages.name, languages.value
FROM languages
WHERE languages.lang = 'EN";
AND UNIX_TIMESTAMP(languages.ts_update) >= ".$_SESSION["LAST_LANG_CHECK"];

For this to work, the 'ts_update' field of the appropriate GLOBAL_UPDATE_TS record must be updated every time another record on this table is inserted or updated.

So I created the following triggers :

CREATE TRIGGER GUTS_I
BEFORE INSERT
ON languages
FOR EACH ROW
BEGIN
    UPDATE languages 
    SET languages.ts_update = NEW.ts_update 
    WHERE languages.name = 'GLOBAL_UPDATE_TS' AND languages.lang = NEW.lang; 
END

and :

CREATE TRIGGER GUTS_U
BEFORE UPDATE
ON languages
FOR EACH ROW
BEGIN
    IF NEW.name != 'GLOBAL_UPDATE_TS' THEN
    UPDATE languages 
    SET languages.ts_update = NEW.ts_update 
    WHERE languages.name = 'GLOBAL_UPDATE_TS' 
    AND languages.lang = NEW.lang; 
    END IF;
END

When I create a new record, I get the following error :

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

I'm quite new to triggers. I searched for this error and learned that MySQL couldn't handle triggers on the same table, but that post was from 2010 so is it still true or did I do something wrong ?

Any help and tips would be greatly appreciated, thank you for reading :D

I tried both AFTER or BEFORE UPDATE/INSRERT but the same error occurs.

0

There are 0 best solutions below