Oracle SQL Trigger insert new records based on a insert column value

Question

I'm trying to create a trigger for a Oracle table. Here are the requirements I have two tables Books, Copies (Books & copies have a 1 to n relationship. Each book can have 0 to n copies)

Book Table:

CREATE TABLE Book
  (
    book_id      INTEGER NOT NULL ,
    isbn         VARCHAR2 (20) NOT NULL,
    publisher_id INTEGER NOT NULL ,
    tittle       VARCHAR2 (100) NOT NULL ,
    cat_id       INTEGER NOT NULL ,
    no_of_copies INTEGER NOT NULL ,
    ....
    CONSTRAINT isbn_unique UNIQUE (isbn),
    CONSTRAINT shelf_letter_unique UNIQUE (shelf_letter, call_number) 
  ) ;

Copies Table

CREATE TABLE Copies
  (
    copy_id     INTEGER NOT NULL ,
    book_id     INTEGER NOT NULL ,
    copy_number INTEGER NOT NULL,
    constraint copy_number_unique unique(book_id,copy_number)
  ) ;

The trigger (on update,edit of a the Book table) should add its corresponding copy records into Copies table. So if a insert into Books table has Book.no_of_copies as 5 then five new records should be inserted into the Copies table..

What? And how should we decide `copy_id`? And if `no_of_copies` is edited e.g. from 5 to 3, which rows of copies should be deleted? — Passerby, Sep 30 '13 at 03:54
copy_id is the primary key and is on Auto Increment. Regarding 5 to 3 reduction, the requirement says that it should throw an application error (so basically it's not allowed). But if it's increased from 5 to 7, two more records should be added. Thanks! — user6123723, Sep 30 '13 at 04:21

Muhammad Talha · Answer 1 · 2013-09-30T06:03:29.633

Below code works for INSERT and UPDATE in table BOOK. It would insert rows in COPIES table only when either a new row is inserted in table BOOK, or an existing row in table BOOK is updated with no_of_copies greater than its current value.

Table creation:

CREATE TABLE Book
(
book_id      INTEGER NOT NULL ,
isbn         VARCHAR2 (20) NOT NULL,
publisher_id INTEGER NOT NULL ,
tittle       VARCHAR2 (100) NOT NULL ,
cat_id       INTEGER NOT NULL ,
no_of_copies INTEGER NOT NULL ,
CONSTRAINT isbn_unique UNIQUE (isbn) 
) ;

CREATE TABLE Copies
(
copy_id     INTEGER NOT NULL ,
book_id     INTEGER NOT NULL ,
copy_number INTEGER NOT NULL,
constraint copy_number_unique unique(book_id,copy_number)
);

CREATE SEQUENCE COPY_SEQ
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
NOCACHE;

Trigger:

CREATE OR REPLACE TRIGGER TR_TEST
BEFORE INSERT OR UPDATE ON BOOK
FOR EACH ROW
DECLARE
V_CURR_COPIES    NUMBER;
V_COUNT          NUMBER := 0;

BEGIN

 IF  :NEW.NO_OF_COPIES > NVL(:OLD.NO_OF_COPIES, 0) THEN
     SELECT COUNT(1)
     INTO   V_CURR_COPIES   --# of rows in COPIES table for a particular book.
     FROM   COPIES C
     WHERE  C.BOOK_ID = :NEW.BOOK_ID;

     WHILE  V_COUNT < :NEW.NO_OF_COPIES - V_CURR_COPIES
     LOOP
         INSERT INTO COPIES
         (
         COPY_ID,
         BOOK_ID,
         COPY_NUMBER
         )
         SELECT COPY_SEQ.NEXTVAL,
                :NEW.BOOK_ID,
                V_COUNT + V_CURR_COPIES + 1
         FROM   DUAL;

         V_COUNT := V_COUNT + 1;
     END LOOP;
 END IF;
END;

Testing:

INSERT INTO BOOK
VALUES (1, 'ABCDEF', 2, 'TEST BOOK', 1, 3);

UPDATE BOOK B
SET    B.NO_OF_COPIES = 4
WHERE  B.BOOK_ID = 1;

score 1 · Accepted Answer · edited May 23 '17 at 11:50

This is kinda long, but actually quite straightforward.

Tested on a Oracle 10gR2 setup.

Table:

CREATE TABLE books
(
  book_id INTEGER NOT NULL,
  no_of_copies INTEGER NOT NULL,
  CONSTRAINT pk_book_id PRIMARY KEY (book_id)
);

CREATE TABLE copies
(
  book_id INTEGER NOT NULL,
  copy_no INTEGER NOT NULL,
  CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE
);

Then trigger:

CREATE TRIGGER tri_books_add
  AFTER INSERT ON books
  FOR EACH ROW
DECLARE
  num INTEGER:=1;
BEGIN
  IF :new.no_of_copies>0 THEN
    WHILE num<=:new.no_of_copies LOOP
      INSERT INTO copies (book_id,copy_no) VALUES (:new.book_id,num);
      num:=num+1;
    END LOOP;
  END IF;
END;
/

CREATE TRIGGER tri_books_edit
  BEFORE UPDATE ON books
  FOR EACH ROW
DECLARE
  num INTEGER:=1;
BEGIN
  IF :new.no_of_copies<:old.no_of_copies THEN
    RAISE_APPLICATION_ERROR(-20001,'Decrease of copy number prohibited.');
  ELSIF :new.no_of_copies>:old.no_of_copies THEN
    SELECT max(copy_no)+1 INTO num FROM copies WHERE book_id=:old.book_id;
    WHILE num<=:new.no_of_copies LOOP
      INSERT INTO copies (book_id,copy_no) VALUES (:old.book_id,num);
      num:=num+1;
    END LOOP;
  END IF;
END;
/

What the trigger do:

For the tri_books_add
1. use a num to "remember" copy_no;
2. use a WHILE-LOOP statement to add copies.
For the tri_books_edit
1. use a num to "remember" copy_no;
2. check if new no_of_copies is illegally decreased; if so, raise a custom error;
3. append copies.

The reason I separate books inserting and editing into two triggers, is because I employed a foreign key constraint, so after insert would be needed for inserting (correct me if I'm wrong on this, though).

I then run some test:

INSERT INTO books (book_id,no_of_copies) VALUES (1,3);
INSERT INTO books (book_id,no_of_copies) VALUES (2,5);

SQL> select * from copies;

   BOOK_ID    COPY_NO
---------- ----------
         1          1
         1          2
         1          3
         2          1
         2          2
         2          3
         2          4
         2          5

8 rows selected.

SQL> update books set no_of_copies=5 where book_id=1;

1 row updated.

SQL> select * from copies;

   BOOK_ID    COPY_NO
---------- ----------
         1          1
         1          2
         1          3
         2          1
         2          2
         2          3
         2          4
         2          5
         1          4
         1          5

10 rows selected.

SQL> update books set no_of_copies=3 where book_id=1;
update books set no_of_copies=3 where book_id=1
       *
ERROR at line 1:
ORA-20001: Decrease of copy number prohibited.
ORA-06512: at "LINEQZ.TRI_BOOKS_EDIT", line 5
ORA-04088: error during execution of trigger 'LINEQZ.TRI_BOOKS_EDIT'

(I don't seem to be able to make sqlfiddle to work on triggers so no online demos, sorry.)

Awesome. Can appreciate you enough for this detailed answer. Thanks for your hard work! I'm now even more motivated to learn Oracle programming. — user6123723, Sep 30 '13 at 20:59

score 1 · Answer 3 · answered Sep 30 '13 at 05:35

create or replace 
trigger BOOK_TRIGGER 
AFTER INSERT ON BOOK 
FOR EACH ROW 
DECLARE L_COPIES NUMBER:= :NEW.NO_OF_COPIES;
BEGIN
     FOR I IN 1..5
     LOOP
          INSERT
          INTO COPIES
               (
                    COPY_ID,
                    BOOK_ID,
                    COPY_NUMBER
               )
               VALUES
               (
                    1, -- your copy sequence
                    :new.book_id,
                    i
               );
     END LOOP;
END;

Thank you. Much apreciated! – user6123723 Sep 30 '13 at 20:58 — user6123723, Sep 30 '13 at 20:58

Oracle SQL Trigger insert new records based on a insert column value

3 Answers3