1

I wrote a trigger in mysql that checks to see if a person is able to check out another library item. The checkout limit I have set is 3. I need to convert this to an Oracle trigger but am having lots of problems.

Here is my MySQL code:

DELIMITER //
DROP TRIGGER IF EXISTS library.CheckBorrowsTable//
CREATE TRIGGER CheckBorrowsTable
BEFORE INSERT ON library.Borrows
FOR EACH ROW
BEGIN
IF ((SELECT COUNT(*) FROM library.Borrows WHERE libraryID = new.libraryID) >= 3)THEN
SET new = NULL;
END IF;
END//
DELIMITER ;

Here is my Oracle code:

IF((SELECT COUNT(libraryID) FROM Borrows WHERE libraryID = :NEW.libraryID) >= 3) THEN
:NEW = NULL;
END IF;

There are other portions to my Oracle code but Oracle Express Edition adds those parts (Begin, End etc...)

Improve this question

1 Answer 1

Reset to default
3

A row-level trigger on table A (i.e. library.borrows) cannot query table A. If you do so, you will get a mutating table exception (unless you can guarantee that you will only ever do single-row inserts with the VALUES clause). So that would not be considered a good development practice in Oracle.

The most logical way to implement this sort of requirement would not be via a trigger. Instead, if your application calls a stored procedure API, you would have a stored procedure (i.e. CHECK_OUT) that first queries the table to determine how many books an individual has checked out and inserts the row into the BORROWS table only if the patron is below his or her limit.

A second approach is to store the number of items checked out in a separate table. If you had a PATRONS table with a NUM_CHECKED_OUT column and your BORROWS table had a PATRON_ID to indicate who had borrowed the book, your trigger could do something like

CREATE OR REPLACE TRIGGER CheckBorrowsTable
  BEFORE INSERT ON library.borrows
  FOR EACH ROW
BEGIN
  UPDATE patrons p
     SET p.num_checked_out = p.num_checked_out + 1
   WHERE p.patron_id = :new.patron_id
END;

along with a CHECK constraint on the PATRONS table to ensure that NUM_CHECKED_OUT never exceeds 3.

Barring that, it is possible, though rather cumbersome, to work around the mutating table error with the "three trigger solution".

  1. A BEFORE INSERT statement level trigger clears out a collection that you have created in a package.
  2. A BEFORE INSERT row level trigger writes the primary key (or ROWID) of the row being modified into the collection.
  3. An AFTER INSERT statement level trigger reads the data from the collection, queries the table, and determines if any of the inserts have violated the business rule. If they did, throw an exception.

As you might imagine, however, the three trigger solution involves a fair number of moving pieces so it's not particularly advisable.

You can also implement this sort of thing with a fast refreshable materialized view, but I don't believe that is an option in the express edition of the database (though I could be wrong on that).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You should note that in a multi-user scenario this will not work - two sessions could insert rows for the same patron, and both will update the patrons table to the same value, because they won't see the other session's update. You would need to serialise access to the patrons table (e.g. CHECK_OUT first locks the row, updates it, inserts the book, then commits (which releases the lock)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.