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; |