我有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操作第二个触发器,但仍然没有帮助。同样的错误,不能继续工作
暂无答案!
目前还没有任何答案,快来回答吧!