Used MySQL - 5.7 - x64

Main table 1

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 ;

Trigger for logging

 CREATE DEFINER=`root`@`%` TRIGGER `trg_02_1_test_before_update` BEFORE UPDATE ON `trg_02_1_test` FOR EACH ROW BEGIN INSERT INTO trglog Set id_tbl_02_n_log = NEW.id_tbl_02_1, tbl_02_pol_content_log = NEW.pol_4_txt_tbl_02_1; END

It is supposed to put data from several tables in the log.

Question.
How to make the log:

in the tbl_02_name_log field - the name of the table in which the update takes place was substituted;
- in the tbl_02_pol_name_log field - the name of the field in which the update occurs is substituted;

?

    1 answer 1

    How to make the log:

    in the tbl_02_name_log field - the name of the table in which the update takes place was substituted;

    Copy the table name from the trigger header (the first line of your code, between the words ON and FOR). Only as a string literal.

    • in the tbl_02_pol_name_log field - the name of the field in which the update takes place was substituted;

    Write an INSERT for each table field, substituting the name of the field currently being processed as a literal. Accordingly, there will be as many INSERT queries as in the table of fields (or to collect dynamic text to add several records, and then execute a single query).

    And in order not to write too much - compare OLD.f and NEW.f, and perform INSERT only if inequality occurs. Template:

     CREATE TRIGGER triggername AFTER UPDATE ON tablename FOR EACH ROW BEGIN IF OLD.fieldX <=> NEW.fieldX THEN INSERT INTO log SET fieldname = 'fieldX' , oldvalue = OLD.fieldX , newvalue = NEW.fieldX; END IF; /* one IF per each FIELD */ END;
    • @ Akina Produces a syntax error - 1064 cm. Screen prntscr.com/e9tg13 / Question. What could be the problem? [syntax = mysql] delimiter // CREATE TRIGGER trg_02_1_test_before_update BEFORE UPDATE ON trg_02_1_test FOR EACH ROW BEGIN / * field pol_4_txt_tbl_02_1 * / IF (NOT OLD.pol_4_txt_tbl_02_1 <=> NEW.pol_4_txt_tbl_02_1) THEN INSERT INTO trglog Set id_tbl_02_n_log = NEW.id_tbl_02_1, tbl_02_pol_content_log = NEW.pol_4_txt_tbl_02_1, tbl_02_pol_name_log = 'pol_4_txt_tbl_02_1'; END IF; tbl_02_name_log = 'trg_02_1_test'; END // [/ syntax] - koverflow
    • one
      And that for crap between END IF; and END// ? - Akina
    • I’m still driving in ... an experimental solution ... I thought: for each field there will be an IF, then why write the name of the table in each IF. Maybe just make it once .... Ie depending on the situation (on which field is updated) this or that IF will work, and the line for which specifies the name of the table in which the update occurs will be one for the entire trigger - koverflow