业务场景:
为订单定制默认的配送日期,比如说下单日期延迟2天。
拟采用触发器解决这个问题。代码示意如下:DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `customer_order`
(
customer_order_id VARCHAR(32),
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
delivery_date TIMESTAMP
);
ALTER TABLE `customer_order` ADD CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY(`CUSTOMER_ORDER_ID`);
DELIMITER //
CREATE TRIGGER `TRI_CUSTOMER_ORDER_DELIVERY_DATE_AFTER_INSERT` AFTER INSERT ON `customer_order`
FOR EACH ROW
UPDATE `customer_order` SET `delivery_date`=(DATE_ADD(NOW(),INTERVAL 2 DAY)) WHERE `customer_order`.`customer_order_id` = NEW.`customer_order_id`;
//DELIMITER ;测试数据:
INSERT INTO `customer_order`(`customer_order_id`) VALUES('1');异常:
Can't update table `customer_order` in stored function/trigger because it is already used by statement which invoked this stored function /trigger以上。
为订单定制默认的配送日期,比如说下单日期延迟2天。
拟采用触发器解决这个问题。代码示意如下:DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `customer_order`
(
customer_order_id VARCHAR(32),
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
delivery_date TIMESTAMP
);
ALTER TABLE `customer_order` ADD CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY(`CUSTOMER_ORDER_ID`);
DELIMITER //
CREATE TRIGGER `TRI_CUSTOMER_ORDER_DELIVERY_DATE_AFTER_INSERT` AFTER INSERT ON `customer_order`
FOR EACH ROW
UPDATE `customer_order` SET `delivery_date`=(DATE_ADD(NOW(),INTERVAL 2 DAY)) WHERE `customer_order`.`customer_order_id` = NEW.`customer_order_id`;
//DELIMITER ;测试数据:
INSERT INTO `customer_order`(`customer_order_id`) VALUES('1');异常:
Can't update table `customer_order` in stored function/trigger because it is already used by statement which invoked this stored function /trigger以上。
FOR EACH ROW
set new.delivery_date=DATE_ADD(NOW(),INTERVAL 2 DAY;