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.