I need to create a trigger which checks if the count of authors for books in category 'fitness' is >3 on insert to the table, and if true, denies the insert.
I need to join two tables in order to get the count.
Table: 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;
Table: BOOKAUTHOR:
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;
I can query the two tables to get what I need:
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
;
However, I am new to triggers and do not know the proper syntax to get the desired result.
My trigger so far looks like:
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;
/
obviously this doesn't work, however I lack the knowledge to resolve it, and the knowledge of what to ask in order to find a tutorial. This seems like a very niche problem.
What I do not know is how to declare the statement such that when I run the following, the trigger will stop the insert:
INSERT INTO BOOKAUTHOR VALUES ('9000000099', 'P105');
Any guidance would be greatly appreciated!
I think I need to declare a temporary table inside the trigger to hold the outputs of the select statement, though I suspect this is not a great approach? And I am unsure how to use the :new prefix in here, but I also suspect I need to?
One insert statement can add any number of rows to a table. As long as the insert is in progress, the state of the table is undefined. You cannot select the number of rows for instance, because the result would not be deterministic, as one row you want to insert could be inserted as the first row or the second or whatever.
A simple solution is to make this an after statement trigger:
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;
/
This, however, will check every fitness book until it finds one with more than three authors. The DBMS will probably count all authors for all fitness books first and then read that result row by row, to see if it finds a count greater than 3. This is rather much work, because usually we will insert one bookauthor, so there would just be one book to check really.
The solution to this is a compound trigger, that will have an AFTER ROW part to remember the book(s) in an array and an AFTER STATEMENT part to check only the books in the array.
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;
/