Как сделать триггер update, чтобы он прописывал в журнал “имя таблицы” и “поле”? - MySQL

389

20 февраля 2017, 17:04

Используется MySQL - 5.7 - х64

Основная таблица 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
;

Таблица журнала

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
;

Триггер для журналирования

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

Предполагается в журнал помещать данные из нескольких таблиц.

Вопрос.
Как сделать, чтобы в журнал:

в поле tbl_02_name_log - подставлялось наименование таблицы в которой происходит обновление;
- в поле tbl_02_pol_name_log - подставлялось наименование поля в которой происходит обновление;

Answer 1

Как сделать, чтобы в журнал:

в поле tbl_02_name_log - подставлялось наименование таблицы в которой происходит обновление;

Скопировать имя таблицы из заголовка триггера (первая строка твоего кода, между словами ON и FOR). Только как строковый литерал.

в поле tbl_02_pol_name_log - подставлялось наименование поля в которой происходит обновление;

Написать INSERT для каждого поля таблицы, подставляя имя обрабатываемого в данный момент поля как литерал. Соответственно запросов INSERT будет столько, сколько в таблице полей (или собирать динамический текст на добавление нескольких записей, а потом единым запросом выполнить).

А чтобы не писАть лишнего - сравнивай OLD.f и NEW.f, и выполняй INSERT только при неравенстве. Шаблонно:

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;