关于MySQL触发器的问题 10C
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.

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

1个回答

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问