Example 12-17: INSTEAD OF Trigger handles Book datagram inserts

CREATE TRIGGER instead_of_insert_on_Books
INSTEAD OF INSERT ON Books
FOR EACH ROW
DECLARE
  theAuthorId  NUMBER;
  theISBN      NUMBER;
BEGIN
  -- Check if the author being inserted already exists in the AUTHOR table.
  BEGIN
    SELECT id
      INTO theAuthorId
      FROM author_table
     WHERE first_name = :new.Book.Author.First
       AND last_name  = :new.Book.Author.Last;
  EXCEPTION
    -- If author not found, create a new row in the author table
    WHEN NO_DATA_FOUND THEN
      INSERT INTO author_table ( id, first_name, last_name )
             VALUES ( authorseq.nextval, 
                      :new.Book.Author.First,
                      :new.Book.Author.Last )
        RETURNING id INTO theAuthorId;
  END;
  -- Check if a Book already exists in the Book table with this ISBN
  SELECT isbn
    INTO theISBN
    FROM book_table
   WHERE isbn = :new.Book.ISBN;
  -- We found an existing Book, so *update* Book instead of inserting.
  UPDATE Book_table
     SET title     = :new.Book.Title, 
         price     = :new.Book.Price, 
         author_id = theAuthorId
   WHERE isbn      = :new.Book.ISBN;
EXCEPTION
  -- If no existing Book found with the new ISBN, then
  -- insert the new Book into the Book table.
  WHEN NO_DATA_FOUND THEN
  -- Insert the new Book into the underlying Book table
  INSERT INTO Book_table (isbn,title,price,author_id)
         VALUES (:new.Book.ISBN,:new.Book.Title,:new.Book.Price, theAuthorId );
END;