sql触发器两个表不为空

7xzttuei 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(388)

CREATE TRIGGER bookAvailDelete after delete on Auction
for each row
Begin
if (Select count(OLD.isbn) from Auction a > 0)
update Book b
set available =false
where OLD.isbn = isbn
;

我有两张table，拍卖和预订。拍卖有参考书isbn主键的isbn键。很多拍卖都可以指向同一本书，所以我希望这个触发器检查每一次拍卖删除，看看这是否是该书的最后一次拍卖isbn。如果是，则将book中的“available”键设置为false。

sql mysql jsp triggers

来源：https://stackoverflow.com/questions/49821550/sql-trigger-two-tables-not-empty

3条答案

按热度按时间

2skhul331#

请在下面编译和测试。使用一个变量作为计数，如果在末尾也放end。

delimiter //
CREATE TRIGGER bookAvailDelete after delete on Auction
for each row
Begin
DECLARE updatecount INT;
set updatecount = ( Select count(*) from Auction a where a.isbn = OLD.isbn);
if updatecount > 0 then
update Book b
set b.available =false
where b.isbn = OLD.isbn;
end if;
End;//

赞(0）回复(0）举报 2021-06-21

c8ib6hqw2#

假设可用值为int：

drop trigger bookAvailDelete;

DELIMITER //
CREATE TRIGGER bookAvailDelete after delete on Auction
    for each row begin
        if NOT EXISTS(Select * from Auction where isbn = old.isbn)
        then
            update Book b set b.available = 0 where b.isbn = old.isbn;
        end if; 
    end;

赞(0）回复(0）举报 2021-06-21

uklbhaso3#

通常，除非有性能方面的原因，否则您只需使用如下查询动态计算（而不是存储）：

SELECT b.*
   , EXISTS(SELECT * FROM Auction a WHERE a.isbn = b.isbn) AS available
FROM Book b

或（我的首选格式）：

SELECT b.*, (a.isbn IS NOT NULL) AS available
FROM Book b
LEFT JOIN (SELECT DISTINCT isbn FROM Auction) AS a ON b.isbn = a.isbn
;

赞(0）回复(0）举报 2021-06-21

我来回答

sql触发器两个表不为空

3条答案

相关问题

热门标签

最新问答