Z
Z
Zakharov Alexander2017-02-02 15:27:42
MySQL
Zakharov Alexander, 2017-02-02 15:27:42

MySql trigger how to check that a field is set when updating?

Hello.
I am writing a trigger for mysql to update a record. To determine the validity of the original data, you need to make sure that certain fields are specified in the update operation:

CREATE TABLE dbcv.cv_translates (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id записи в таблице (ключ)',
  cv_users_id bigint(20) UNSIGNED NOT NULL COMMENT 'id пользователя',
  translate_description varchar(255) DEFAULT '' COMMENT 'Наименование языка (не обязательно)',
  json_version bigint(20) NOT NULL COMMENT 'версия. Обязательно сравнивать перед записью и инкриментировать после записи (выполняется триггером)',
  json_data json NOT NULL COMMENT 'Данные резюме',
  json_history json DEFAULT NULL COMMENT 'История изменений записи json_data',
  date_create timestamp NULL DEFAULT NULL COMMENT 'Дата создания резюме',
  date_update timestamp NULL DEFAULT NULL COMMENT 'Дата изменения',
  PRIMARY KEY (id),
  UNIQUE INDEX UK_cv_translates_id (id),
  CONSTRAINT FK_cv_translates_cv_users_id FOREIGN KEY (cv_users_id)
  REFERENCES dbcv.cv_users (id) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 142
AVG_ROW_LENGTH = 93485
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Таблица данных с переводами резюме пользователей'
ROW_FORMAT = DYNAMIC;

Trigger:
CREATE 
  DEFINER = 'root'@'localhost'
TRIGGER dbcv.cv_translates_trigger_update
  BEFORE UPDATE
  ON dbcv.cv_translates
  FOR EACH ROW
BEGIN
  if( NEW.json_data!=OLD.json_data) THEN
    if( NEW.json_version != OLD.json_version ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ошибка сохранения резюме. Версии данных должны совпадать. (триггер cv_users_trigger_update)';
    END if;
    SET NEW.json_hash=SHA1(NEW.json_data);
    SET NEW.json_version = OLD.json_version+1;
    SET @j = JSON_SET( '{}', '$.json_data', NEW.json_data, '$.json_version', NEW.json_version, '$.json_data_updated', DATE_FORMAT(NOW(), '%Y-%m-%d %T'));
    SET NEW.json_history = JSON_MERGE(OLD.json_history, @j);
    SET NEW.date_update = NOW();
  END if;
END

The question revolves around the UPDATE operation:
UPDATE dbcv.cv_translates SET json_data=?, translate_description=? WHERE id=?;

You need to throw an error if the user did not specify the json_version field in the update request . I assume that the condition should be inserted into the trigger in the line "if( NEW.json_version != OLD.json_version ) THEN". But I'm not exactly sure if there is even an opportunity to check such a condition. Currently, if the json_version field is not set, then everything works like NEW.json_version == OLD.json_version .
Question - is it possible to check that the json_version field is set explicitly in the UPDATE request?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question