Oracle PL/SQL Best Practices

Name

MOD-12: Raise exceptions to report on do-nothing INSTEAD OF triggers.

Synopsis

If you execute an UPDATE statement and it doesn’t identify any rows to update, Oracle doesn’t raise an error. In many cases, that is fine. In other cases, it might indicate an error. The situation is the same with INSTEAD OF triggers. These triggers allow you to specify an alternative operation that will take place instead of the normal DML action with which the trigger is associated.

If the INSTEAD OF trigger doesn’t execute any DML at all but doesn’t raise an exception, it doesn’t report any error back to the calling program. While in some cases this may be the desired behavior, you usually want to raise an exception, and perhaps also log the fact that a failure occurred.

Example

Consider the following rather selfish trigger. I have created a view called best_sellers that sits on top of the book table. When you insert a row into best_sellers, it actually inserts a row only if the publisher of the book is O’Reilly & Associates!

CREATE OR REPLACE TRIGGER instead_of_best_sellers
   INSTEAD OF INSERT
   ON best_sellers
BEGIN
   IF :new.publisher = 'O''REILLY & ASSOCIATES'
   THEN
      INSERT INTO book (
         author, title, isbn, publisher)
      VALUES (
         :new.author, :new.title, :new.isbn, :new.publisher);
   END IF;
END;

That’s an unethical thing to do, but with the way the trigger is written, there’s no immediate notification that a best seller by, say, Oracle Press, wasn’t added to the table. Here’s a more principled and appropriate ...