关于MySQL触发器的问题

Question

CREATE TABLE `retailcatalog` (
  `UUID` varchar(38) NOT NULL COMMENT '唯一标识，自动生成，为主键',
  `PRODUCTUUID` varchar(38) NOT NULL COMMENT '商品唯一标识，需要创建索引',
  `BEGINDATE` date NOT NULL COMMENT '起始时间',
  `ENDDATE` date NOT NULL COMMENT '截止时间',
  `PRICE` double(19,4) NOT NULL COMMENT '销售价',
  PRIMARY KEY (`UUID`),
  UNIQUE KEY `UQ_RETAILCATALOG_RetailcatalogID` (`UUID`,`PRODUCTUUID`),
  KEY `IX_retailcatalog_productUUID` (`PRODUCTUUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
  `UUID` varchar(38) NOT NULL,
  `CODE` varchar(20) NOT NULL COMMENT '代码，需要创建索引',
  `NAME` varchar(100) NOT NULL COMMENT '名称',
  `ORDERPRICE` double(19,4) DEFAULT NULL COMMENT '采购价',
  `SALEPRICE` double(19,4) DEFAULT NULL COMMENT '销售价（数据来源于 RETAILCATALOG 表）',
  `STATE` int(11) DEFAULT NULL COMMENT '状态 (0 为使用中， 999 为已删除 )',
  PRIMARY KEY (`UUID`),
  UNIQUE KEY `UUID` (`UUID`),
  KEY `IX_product_code` (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有两张表，retailcatalog和product
有一个存储过程

/*
 *将 RETAILCATALOG 上的 PRICE 数据同步到 PRODUCT 表上的 SALEPRICE
*/
drop procedure if exists proc_product_saleprice_state_update;
create procedure proc_product_saleprice_state_update(
    in nowday date
)
begin
    update
    product p, retailcatalog r 
    set
    p.SALEPRICE=r.PRICE,p.STATE=0
    where
    r.PRODUCTUUID = p.UUID and nowday >= r.BEGINDATE and nowday <= r.ENDDATE;   

    update
    product p, retailcatalog r 
    set 
    p.SALEPRICE=0,p.STATE=999
    where
    (r.PRODUCTUUID = p.UUID and (nowday < r.BEGINDATE or nowday > r.ENDDATE)) or 
  not exists(
    select 
    1 
    from 
    retailcatalog r
    where
    p.UUID = r.PRODUCTUUID);
end;

a. 编写一个触发器，要求在修改销售目录（ RETAILCATALOG ）数据的 PRICE 之后，修改 PRODUCT 表的 SALEPRICE
b. 编写一个触发器，要求在修改商品表（ PRODUCT ）数据的状态（ STATE ）之后，如果发现 STATE 为 999 ，则删除对应的 RETAILCATALOG

以下是我写的触发器

/*
 *在修改销售目录（ RETAILCATALOG ）数据的 PRICE 之后，修改 PRODUCT 表的 SALEPRICE
*/ DROP TRIGGER
IF
    EXISTS retailcatalog_after_update_price_on_product;
CREATE TRIGGER retailcatalog_after_update_price_on_product AFTER UPDATE ON retailcatalog FOR EACH ROW
BEGIN
    IF
        new.PRICE <> old.PRICE THEN
        UPDATE product p 
        SET p.SALEPRICE = new.PRICE 
        WHERE
            p.UUID = new.PRODUCTUUID;       
    END IF;
END;

/*
 *在修改商品表（ PRODUCT ）数据的状态（ STATE ）之后，如果发现 STATE 为 999 ，则删除对应的 RETAILCATALOG
*/
DROP TRIGGER
IF
    EXISTS product_after_update_state_on_retailcatalog;
CREATE TRIGGER product_after_update_state_on_retailcatalog AFTER UPDATE ON product FOR EACH ROW
BEGIN
    IF
        new.STATE = 999 THEN
            DELETE r 
        FROM
            retailcatalog r 
        WHERE
            ( r.PRODUCTUUID = new.UUID AND new.STATE = 999 );

    END IF;
END;

当我运行存储过程时
call proc_product_salepriceAndstate_update(CURDATE());

数据库报错
call proc_product_salepriceAndstate_update(CURDATE())

1442 - Can't update table 'retailcatalog' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

各位大神能指导下我应该如何改进吗？

Answer 1

https://blog.csdn.net/qq_35347459/article/details/70857919