Used by
MySQL - 5.7 - x64
HeidiSQL -9.4

When saving the trigger "before * update" error 1064 appears.
"Syntax; syntax; syntax; syntax;

I bring the trigger.

CREATE TRIGGER `trg_02_1_test_before_update` BEFORE UPDATE ON `trg_02_1_test` FOR EACH ROW BEGIN INSERT INTO trg Set id_tbl_02_n_log = NEW.id_tbl_02_1; END;

Question.
How can I solve the problem?

Main table

 CREATE TABLE `trg_02_1_test` ( `id_tbl_02_1` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `cur_datetime_tbl_02_1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `pol_1_date_tbl_02_1` DATE NULL DEFAULT NULL, `pol_2_enum_tbl_02_1` ENUM('Y','N') NULL DEFAULT NULL, `pol_3_longtxt_tbl_02_1` LONGTEXT NULL, `pol_4_txt_tbl_02_1` TEXT NULL, `pol_5_int_tbl_02_1` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id_tbl_02_1`) ) LONGTEXT\r\n' COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DYNAMIC AUTO_INCREMENT=7 ;

Log table

 CREATE TABLE `trg` ( `datetime_log` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `tbl_02_name_log` TEXT NULL, `id_tbl_02_n_log` INT(11) NULL DEFAULT NULL, `tbl_02_pol_name_log` TEXT NULL, `tbl_02_pol_content_log` TEXT NULL ) LONGTEXT\r\n' COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DYNAMIC ;

enter image description here

  • @Mike I didn’t understand how to apply this .. I’m using HeidiSQL .. And it automatically starts the initial code and doesn’t work out .. prntscr.com/e9j70d - koverflow
  • 2
    Try to write to the beginning to a file with instructions delimiter and execute with the help of mysql, i.e. not from this medium. and see if there is an error or not. And then dig further. check insert with these table / field names with a fixed value instead of new - Mike
  • @Mike I will understand ... You do not know why in the working and non-working version of triggers the color of the table names is different? See the screen at the link prntscr.com/e9jz4b - koverflow
  • one
    trg can reserve a word in MySQL and if the table is so called it must be enclosed in reverse apostrophes - Mike
  • @Mike Renamed - the color has not changed .. Enclosed in apostrophes, the color has changed .. prntscr.com/e9ka6z - koverflow

2 answers 2

Try

 CREATE TRIGGER `trg_02_1_test_before_update` BEFORE UPDATE ON `trg_02_1_test` FOR EACH ROW BEGIN INSERT INTO trg Set @id_tbl_02_n_log = NEW.id_tbl_02_1; END;
  • when inserted into a table (see the previous line, set is its continuation), assign the value to a variable ... well, well ... - Mike
  • Well, it will be another mistake ahah - Zakhar Telyatnikov
  • @Zakhar Telyatnikov The same mistake - koverflow

The problem was solved by the type design

 INSERT INTO tab1(fld1) VALUES(val1)

Those. the trigger in my case will look like this

 CREATE DEFINER=`root`@`%` TRIGGER `trg_02_1_test_before_update` BEFORE UPDATE ON `trg_02_1_test` FOR EACH ROW BEGIN /*РАБОТАЕТ ДАТА*/ IF (NOT OLD.pol_1_date_tbl_02_1 <=> NEW.pol_1_date_tbl_02_1) THEN INSERT INTO trg_02_00_log(tbl_02_tbl_name_log, id_tbl_02_n_log, tbl_02_pol_name_log, tbl_02_pol_content_log) VALUES('trg_02_1_test', NEW.id_tbl_02_1, 'pol_1_date_tbl_02_1' ,NEW.pol_1_date_tbl_02_1); END IF; /*РАБОТАЕТ enum*/ IF (NOT OLD.pol_2_enum_tbl_02_1 <=> NEW.pol_2_enum_tbl_02_1) THEN INSERT INTO trg_02_00_log(tbl_02_tbl_name_log, id_tbl_02_n_log, tbl_02_pol_name_log, tbl_02_pol_content_log) VALUES('trg_02_1_test', NEW.id_tbl_02_1, 'tbl_02_pol_name_log' ,NEW.pol_2_enum_tbl_02_1); END IF; /*РАБОТАЕТ longtxt*/ IF (NOT OLD.pol_3_longtxt_tbl_02_1 <=> NEW.pol_3_longtxt_tbl_02_1) THEN INSERT INTO trg_02_00_log(tbl_02_tbl_name_log, id_tbl_02_n_log, tbl_02_pol_name_log, tbl_02_pol_content_log) VALUES('trg_02_1_test', NEW.id_tbl_02_1, 'pol_3_longtxt_tbl_02_1' ,NEW.pol_3_longtxt_tbl_02_1); END IF; /*РАБОТАЕТ ТЕКСТ*/ IF (NOT OLD.pol_4_txt_tbl_02_1 <=> NEW.pol_4_txt_tbl_02_1) THEN INSERT INTO trg_02_00_log(tbl_02_tbl_name_log, id_tbl_02_n_log, tbl_02_pol_name_log, tbl_02_pol_content_log) VALUES('trg_02_1_test', NEW.id_tbl_02_1, 'pol_4_txt_tbl_02_1' ,NEW.pol_4_txt_tbl_02_1); END IF; /*РАБОТАЕТ int*/ IF (NOT OLD.pol_5_int_tbl_02_1 <=> NEW.pol_5_int_tbl_02_1) THEN INSERT INTO trg_02_00_log(tbl_02_tbl_name_log, id_tbl_02_n_log, tbl_02_pol_name_log, tbl_02_pol_content_log) VALUES('trg_02_1_test', NEW.id_tbl_02_1, 'pol_5_int_tbl_02_1' ,NEW.pol_5_int_tbl_02_1); END IF; END