Trigger works only once
Dear Community !
I have a huge problem with my trigger. My aim is to archieve every change on a row that has been proceeded. My trigger looks like follows:
CREATE TRIGGER persons_archive
BEFORE UPDATE ON p_persons
FOR EACH ROW
INSERT INTO p_a_globalized.p_a_persons
VALUES
(OLD.c_id,
CURRENT_TIMESTAMP,
OLD.c_access_level,
OLD.c_username,
OLD.c_email,
OLD.c_name,
OLD.c_vorname,
OLD.c_password,
OLD.c_sex,
OLD.c_birthdate,
OLD.c_sys_language,
OLD.c_titel,
OLD.c_strasse,
OLD.c_nr,
OLD.c_ort,
OLD.c_plz,
OLD.c_versnr,
OLD.c_allow_card,
OLD.c_zahlungsart,
OLD.c_active);
p_persons and p_a_persons are in different databases on the same server and are beeing accessed by the same user having the needed rights.
So what is my problem?
When I execute an Update statement on p_persons __FOR_THE_FIRST_TIME_ then the trigger works fine, _BUT_ on the next time it doesn't.
The primary key of p_a_persons is a set on c_id and c_changed_on so that can't be a duplicate primary key, but that doesn't matter, because it doesn't work without primary key anyway.
The error I am getting on the second update is:
Error
There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:
----BEGIN CUT----
eNpVks+OmzAQxu88xRxWPQHBCSGBqlJRSndR84dCdveIDLisFWMINmmyr7aHPlJfoc6uRFxLPsz8
vplvbE2Uprs0gBWC1RTW0TaAmeuqO4fZHBnZXrE3w1g9ZQHcxVUA4sg63AvS24wWdvfSmSeY2u4C
po7jTRw0QQtAi8CZBjMHykvRylw2EJ07uDM2l+znOoC57djIt4TEvMJ9ZTxmKewyE8L7aLs34SlS
nhtcQhZ+D9P4Xe4i30g2YaCsliqcGclDchWau+zaD9lqdsqHs7EOt/cBEG4N8pe1NN79HpNv4T6C
Kq9ZW2BGX0llV3lHetFyAVm0hzInDaYMvsCn49DKzwfcUPb1WFApDuRicyI/8mAqKccNGZU/hoKW
B6phQc4jbbT8qe3/r7x6aFxSSW4TxLzWe8oeC3GrTfDAKCe8vmb10fpR4k+QP5lqrO3lCJ8p4Rrq
2OuIEFo6GipoL18qLG/eyF96luNZSJed1Gdq5q7jeUjtw9LTH3EROcO8HnB964YH/WsxY+3vvFRL
oQQInh+iNFJp+hGu4028B/T37Q9czz8V/tSu
----END CUT----
----BEGIN RAW----
ERROR: C1 C2 LEN: 344 345 351
STR: �
CVS: $Id: sqlparser.lib.php,v 2.47 2006/01/17 17:02:30 cybot_tm Exp $
MySQL: 5.0.19-standard
USR OS, AGENT, VER: Mac SAFARI 5.0.419
PMA: 2.8.0.3
PHP VER,OS: 5.1.2 Linux
LANG: en-utf-8
SQL: UPDATE p_globalized.p_persons SET c_email = "***" , c_name = "Kubicki" , c_sex = "m" , c_vorname = "Kamil" , c_titel = "***" , c_strasse = "***" , c_nr = "***" , c_ort = "***" , c_plz = "***" , c_birthdate = "1986-11-11" , c_versnr = "***" , c_sys_language = "aut" , c_allow_card = 1 WHERE c_id = 1 LIMIT 1
----END RAW----
SQL query:
UPDATE p_globalized.p_persons SET c_email = "***" , c_name = "Kubicki" , c_sex = "m" , c_vorname = "Kamil" , c_titel = "**" , c_strasse = "***" , c_nr = "***" , c_ort = "***" , c_plz = "***" , c_birthdate = "1986-11-11" , c_versnr = "***" , c_sys_language = "aut" , c_allow_card = 1 WHERE c_id = 1 LIMIT 1
MySQL said:
#1064 - 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 '1' at line 1
Is it a known error? Can you help me? I would really appreciate!!
Thanks a lot!
Regards,
Kamil Kubicki