Oracle SQL -带连接的条件插入触发器

bpsygsoo  于 2023-10-16  发布在  Oracle
关注(0)|答案(2)|浏览(140)

我需要创建一个触发器，它检查在插入到表中时，类别'fitness'中的书籍的作者计数是否>3，如果为true，则拒绝插入。
我需要连接两个表以获得计数。
表：BOOK：

Create table Book
(Book_ISBN CHAR(10) PRIMARY KEY,
Book_Title VARCHAR2(30) NOT NULL,
Book_PubDate DATE NOT NULL,
Pub_ID NUMBER (2) NOT NULL REFERENCES Publisher(Pub_ID),
Book_Cost NUMBER (5,2) NOT NULL,
Book_Retail NUMBER (5,2) NOT NULL,
Book_Category VARCHAR2(12) NOT NULL);

INSERT INTO BOOK VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-01',4,18.75,30.95,'FITNESS');
INSERT INTO BOOK
VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-01',1,14.20,22.00, 'FAMILY LIFE');
INSERT INTO BOOK
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-02',2,37.80,59.95, 'CHILDREN');
INSERT INTO BOOK
VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-99',3,31.40,55.95, 'COMPUTER');
INSERT INTO BOOK
VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-00',4,12.50,19.95,'COOKING');
INSERT INTO BOOK
VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-01',3,47.25,75.95,'COMPUTER');
INSERT INTO BOOK
VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-01',3,21.80,25.00,'COMPUTER');
INSERT INTO BOOK
VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-02',2,37.90,54.50,'COMPUTER');
INSERT INTO BOOK
VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-00',5,48.00,89.95,'FAMILY LIFE');
INSERT INTO BOOK
VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-00',4,19.00,28.75,'COOKING');
INSERT INTO BOOK
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-01',5,5.32,8.95,'CHILDREN');
INSERT INTO BOOK
VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-02',4,17.85,29.95, 'SELF HELP');
INSERT INTO BOOK
VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-99',1,15.40,31.95,'BUSINESS'); 
INSERT INTO BOOK
VALUES ('2147428890','SHORTEST POEMS','01-MAY-01',5,21.85,39.95, 'LITERATURE');

Commit;

表：预订人：

CREATE TABLE BOOKAUTHOR
(Book_ISBN CHAR(10) REFERENCES Book(Book_ISBN),
Author_ID CHAR(4) REFERENCES Author(Author_ID),
CONSTRAINT bookauthor_pk PRIMARY KEY (Book_ISBN,Author_ID));

INSERT INTO BOOKAUTHOR
VALUES ('1059831198','S100');
INSERT INTO BOOKAUTHOR
VALUES ('1059831198','P100');
INSERT INTO BOOKAUTHOR
VALUES ('0401140733','J100');
INSERT INTO BOOKAUTHOR
VALUES ('4981341710','K100');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','P105');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','A100');
INSERT INTO BOOKAUTHOR
VALUES ('8843172113','A105');
INSERT INTO BOOKAUTHOR
VALUES ('3437212490','B100');
INSERT INTO BOOKAUTHOR
VALUES ('3957136468','A100');
INSERT INTO BOOKAUTHOR
VALUES ('1915762492','W100');
INSERT INTO BOOKAUTHOR
VALUES ('1915762492','W105');
INSERT INTO BOOKAUTHOR
VALUES ('9959789321','J100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','R100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','F100');
INSERT INTO BOOKAUTHOR
VALUES ('2491748320','B100');
INSERT INTO BOOKAUTHOR
VALUES ('0299282519','S100');
INSERT INTO BOOKAUTHOR
VALUES ('8117949391','R100');
INSERT INTO BOOKAUTHOR
VALUES ('0132149871','S100');
INSERT INTO BOOKAUTHOR
VALUES ('9247381001','W100');
INSERT INTO BOOKAUTHOR
VALUES ('2147428890','W105');

Commit;

我可以查询这两个表来获得我需要的：

SELECT
    BOOKAUTHOR.book_isbn,
    BOOK.book_category,
    COUNT(BOOKAUTHOR.book_isbn)
FROM BOOKAUTHOR
JOIN BOOK ON BOOK.book_isbn = BOOKAUTHOR.book_isbn
WHERE BOOK.book_category = 'FITNESS'
GROUP BY BOOKAUTHOR.book_isbn, BOOK.book_category
;

然而，我是触发器的新手，不知道获得所需结果的正确语法。
到目前为止，我的触发器看起来像：

CREATE TRIGGER fitness_author_control AFTER INSERT ON BOOKAUTHOR
FOR EACH ROW

DECLARE
    abundant_authors EXCEPTION;
    J Number(2);
    #temp;
        
BEGIN
    SELECT
        BOOKAUTHOR.book_isbn,
        BOOK.book_category,
        COUNT(BOOKAUTHOR.book_isbn) AS "J" 
        INTO temp
    FROM BOOKAUTHOR
    JOIN BOOK ON BOOK.book_isbn = BOOKAUTHOR.book_isbn
    WHERE BOOK.book_category = 'FITNESS'
    GROUP BY BOOKAUTHOR.book_isbn, BOOK.book_category;
        
    IF J > 3
    THEN DBMS_OUTPUT.PUT_LINE ('That is too many authors!');
    RAISE abundant_authors;
    END IF;
    
EXCEPTION
    WHEN abundant_authors
    THEN raise_application_error(-20002, 'I Cannot handle this many authors!');
END;
/

显然这不起作用，但是我缺乏解决它的知识，以及为了找到教程而问什么的知识。这似乎是一个非常小的问题。
我不知道的是如何声明语句，以便当我运行以下命令时，触发器将停止插入：

INSERT INTO BOOKAUTHOR VALUES ('9000000099', 'P105');

任何指导都将不胜感激！
我认为我需要在触发器中声明一个临时表来保存select语句的输出，尽管我怀疑这不是一个好方法。我不确定如何在这里使用：new前缀，但我也怀疑我需要？

oracle

2条答案

按热度按时间
zbdgwd5y

zbdgwd5y1#

一个insert语句可以向表中添加任意数量的行。只要插入正在进行，表的状态就是未定义的。例如，您不能选择行数，因为结果不是确定性的，因为您想要插入的一行可以作为第一行或第二行或其他行插入。
一个简单的解决方案是将其设置为after语句触发器：

CREATE OR REPLACE TRIGGER trg_fitness_author_control
AFTER INSERT ON bookauthor
DECLARE
  v_book_isbn  book.book_isbn%type;
BEGIN
  SELECT MAX(book_isbn)
  INTO v_book_isbn
  FROM
  (
    SELECT book_isbn
    FROM book b
    JOIN bookauthor ba USING (book_isbn)
    WHERE b.book_category = 'FITNESS'
    GROUP BY book_isbn
    HAVING COUNT(*) > 3
    FETCH FIRST ROW ONLY
    );    

  IF v_book_isbn IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Too many authors for fitness book ' || v_book_isbn);
  END IF;
END trg_fitness_author_control;
/

然而，这将检查每一本健身书，直到它找到一本有三个以上作者的书。DBMS可能会首先计算所有健身书籍的所有作者，然后逐行读取结果，看看是否发现计数大于3。这是相当多的工作，因为通常我们会插入一本书的作者，所以实际上只有一本书需要检查。
解决方案是一个复合触发器，它有一个AFTER ROW部分来记住数组中的书，还有一个AFTER STATEMENT部分来只检查数组中的书。

CREATE OR REPLACE TRIGGER trg_fitness_author_control
FOR INSERT ON bookauthor
COMPOUND TRIGGER
  TYPE type_isbns  IS TABLE OF book.book_isbn%TYPE;
  v_isbns          type_isbns := type_isbns();
  v_book_category  book.book_category%TYPE;
  v_count          INTEGER;

  AFTER EACH ROW IS
  BEGIN
    v_isbns.EXTEND;
    v_isbns(v_isbns.COUNT) := :new.book_isbn;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1 .. v_isbns.COUNT LOOP
      SELECT b.book_category, a.cnt
      INTO v_book_category, v_count
      FROM book b
      CROSS APPLY
      (
        SELECT COUNT(*) as cnt 
        FROM bookauthor ba
        WHERE ba.book_isbn = b.book_isbn
      ) a
      WHERE b.book_isbn = v_isbns(i);

      IF v_book_category = 'FITNESS' AND v_count > 3 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Too many authors for fitness book ' || v_isbns(i));
      END IF;
    END LOOP;
  END AFTER STATEMENT;
END trg_fitness_author_control;
/

演示：https://dbfiddle.uk/MtF1NLRQ

赞(0）分享  回复(0）举报  2023-10-16
nnsrf1az

nnsrf1az2#

你没有把你的情况的错误消息，但我认为你的问题是mutation。
当Oracle尝试更改数据（插入/更新/删除）时，此表中数据的状态是未定义的，因此您不能使用此表进行选择。
解决方案的一个可能变体-将触发器类型从“FOR EACH ROW”更改为语句级别，或者遵循链接中的几个建议。

赞(0）分享  回复(0）举报  2023-10-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com