Добрый вечер.
Есть у нас 4 таблицы: клиенты - customers, товары - goods, статус (для товаров) - goods_status и собственно таблица журнала - goods_history_logger
CREATE TABLE IF NOT EXISTS `mydb`.`customers`
(
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_email` TINYTEXT NOT NULL,
`user_password` TINYTEXT NOT NULL,
PRIMARY KEY (`user_id`)
)
CREATE TABLE IF NOT EXISTS `mydb`.`goods`
(
`fk_status_id` TINYINT NOT NULL DEFAULT 0,
`fk_user_id` INT UNSIGNED NOT NULL,
`goods_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`goods_name` TINYTEXT NOT NULL,
`goods_amount` SMALLINT UNSIGNED NOT NULL,
`goods_cost` SMALLINT NOT NULL DEFAULT 0,
`goods_discount` SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (`goods_id`),
CONSTRAINT `fk_status_id`
FOREIGN KEY (`fk_status_id`)
REFERENCES `mydb`.`goods_status` (`status_id`)
CONSTRAINT `fk_user_id`
FOREIGN KEY (`fk_user_id`)
REFERENCES `mydb`.`customers` (`user_id`)
)
CREATE TABLE IF NOT EXISTS `mydb`.`goods_status`
(
`status_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`status_name` TINYTEXT NOT NULL,
PRIMARY KEY (`status_id`)
)
CREATE TABLE IF NOT EXISTS `mydb`.`goods_history_logger`
(
`fk_log_goods_id` INT UNSIGNED NOT NULL,
`fk_log_status_id` TINYINT UNSIGNED NOT NULL, // статус товара (в корзине, оплачен и т.п.)
`fk_log_user_id` INT UNSIGNED NOT NULL // пользователь, вносящий изменения (чаще всего - клиент)
`log_record_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`goods_amount_log_value` VARCHAR(45) NULL,
`goods_cost_log_value` VARCHAR(45) NULL,
`goods_discount_log_value` VARCHAR(45) NULL,
PRIMARY KEY (`log_record_id`),
CONSTRAINT `fk_log_goods_id`
FOREIGN KEY (`fk_log_goods_id`)
REFERENCES `mydb`.`goods` (`goods_id`)
CONSTRAINT `fk_log_user_id`
FOREIGN KEY (`fk_log_user_id`)
REFERENCES `mydb`.`customers` (`user_id`)
CONSTRAINT `fk_log_status_id`
FOREIGN KEY (`fk_log_status_id`)
REFERENCES `mydb`.`goods_status` (`status_id`)
)
Надеюсь, не слишком много букв вышло для этого примера..
И нужно бы нам хранить изменения, происходящие в следующих столбцах таблицы
goods:
(слева - названия столбцов таблицы goods, справа - столбцов из таблицы goods_history_logger)
`fk_status_id` -> `fk_log_status_id`
`goods_amount` -> `goods_amount_log_value`
`goods_cost` -> `goods_cost_log_value`
`goods_discount` -> `goods_discount_log_value`
при изменении данных в любом из столбцов таблицы goods триггер должен записать в журнальную таблицу изменившееся значение. Измениться может как одно значение (например, цена товара), так сразу и несколько (количество, скидка и статус). Отдельный вопрос в том, можно ли настроить триггер на заполнение только лишь изменившихся столбцов (во избежание хранения лишней информации).
Я так полагаю, нужны будут 3 триггера:
AFTER INSERT - записывающий первичное состояние сразу после создания товара
AFTER UPDATE - записывающий все последующие обновления журналируемых столбцов таблицы
AFTER DELETE - вычищающий все записи по данному товару при его удалении из БД
С первым я , наверное, справился бы. А вот второй трудноват.
CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`goods_AFTER_UPDATE` AFTER UPDATE ON `goods` FOR EACH ROW
BEGIN
...
END
Прошу помощи в составлении триггера № 2
Отредактированно sameuser (24.08.2016 20:37:49)
