Answer the question
In order to leave comments, you need to log in
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;
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
UPDATE dbcv.cv_translates SET json_data=?, translate_description=? WHERE id=?;
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question