无法更新存储函数/触发器中的表'bids',因为调用此存储函数/触发器的语句已使用它

ruyhziif  于 2021-06-25  发布在  Mysql
关注(0)|答案(0)|浏览(264)

我有table

CREATE TABLE Bids
 (buyer VARCHAR(20),
 vin VARCHAR(20),
 amount int,
 autobid int,
 upperlimit int,
 PRIMARY KEY(buyer, vin, amount),
 FOREIGN KEY (vin) REFERENCES Auctions(vin)

 );

 CREATE TABLE Notifications
 (buyer VARCHAR(20),
 outbidded VARCHAR(20),
 vin VARCHAR(20),
 amount int,
 PRIMARY KEY(vin, buyer, amount)
 );

以及触发器

DELIMITER $$
CREATE TRIGGER before_bid_insert 
    BEFORE INSERT ON Bids
    FOR EACH ROW 
BEGIN

    SELECT B.amount, B.buyer INTO @amount, @buyer
    FROM Bids AS B WHERE B.vin = NEW.vin AND B.amount = (SELECT MAX(amount) FROM Bids B WHERE B.vin = NEW.vin AND B.buyer != NEW.buyer);

    IF NEW.amount > @amount  THEN
    INSERT INTO Notifications (buyer, outbidded, vin, amount)
    values (NEW.buyer, @buyer, NEW.vin, NEW.amount);

    END IF ;

END$$
DELIMITER ;

 DELIMITER $$
CREATE TRIGGER automatic_bidding
    AFTER INSERT ON Notifications
    FOR EACH ROW 
BEGIN

    SELECT B.amount, B.buyer, B.autobid, B.upperlimit INTO @amount, @buyer, @autobid, @upperlimit
    FROM Bids AS B WHERE B.vin = NEW.vin AND B.amount = (SELECT MAX(amount) FROM Bids B WHERE B.vin = NEW.vin AND B.buyer != NEW.buyer);

    IF NEW.amount > @amount AND @autobid != 0 AND NEW.amount + @autobid <= @upperlimit THEN

    SET @amount = NEW.amount + @autobid;

    INSERT INTO Bids (buyer, vin, amount, autobid, upperlimit)
    values (@buyer, NEW.vin, @amount, @autobid, @upperlimit);

    END IF ;

END$$
DELIMITER ;

但我总是犯这个错误。第一个触发器应该向通知表中添加一个通知,说明用户出价过高。然后,自动出价触发器应该对此做出React,并检查出价失败的用户是否设置了autobid,这将放置一个新的出价。但是,mysql似乎不允许这样做,因为第一个触发器是使用bids表来执行操作的?我怎样才能把这两个动作分开,这样它们就可以互相配合了?
编辑:我尝试更改通知表以包含bids表中的信息,这样我就可以从通知而不是bids操作第二个触发器,但仍然没有帮助。同样的错误,不能继续工作

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题