sql - 插入和连接表以更新后触发

Question

我有3张桌子：

BOOK(bookid primary, nbloans)
COPIES(copyid primary,bookid)
BORROW(bid primary,copyid)

我想在插入借用后创建一个触发器，增加 BOOK.nbloans。我尝试了以下方法：

CREATE OR REPLACE TRIGGER trigger4
AFTER INSERT ON BORROW
FOR EACH ROW   
BEGIN 
   UPDATE BOOK
   SET NBLOANS = NBLOANS + 1
   WHERE BORROW.COPYID=COPIES.COPYID AND  COPIES.BOOKID=BOOK.BOOKID;
END; 

标签： sqloracle

Answer 1

我不确定您的问题是什么，但 SQL 中的触发器通常是指:new和/或:old. 所以我会期待这样的事情：

CREATE OR REPLACE TRIGGER trigger4
AFTER INSERT ON BORROW
FOR EACH ROW   
BEGIN 
   UPDATE BOOK B
       SET NBLOANS = NBLOANS + 1
      WHERE B.BOOKID = (SELECT C.BOOKID
                        FROM COPIES C
                        WHERE C.COPYID = :NEW.COPYID
                       );
END;