CODEWITHSUNDEEP
plsqltriggersoracle-sqldeveloperoracle-xe
Jacob Olson
Jacob Olson

Reputation: 1

How To Create a PL/SQL Trigger That Detects an Inserted or Updated Row and updates a Record in a Different Table?

I am creating a book tracking database for myself that holds information about my books and allows me to keep track of who is borrowing them. I am trying to create a trigger on my Checkouts table that runs if a record is added or updated that will determine if a checkout data has been entered or if a checkin date has been entered and change the "available" field in my Books table to "Y" or "N".

I have created a trigger called "update_book_availablility" on my Checkouts table but I keep getting this error:

"PLS-00103: Encountered the symbol 'end-of-file' when expecting one of the following: ( begin case declare and exception exit for goto if loop mod null pragma raise return select update while with <<continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge standard pipe purge json_object
Errors: check compiler log"

Here is my trigger code:

CREATE OR REPLACE NONEDITIONABLE TRIGGER "UPDATE_BOOK_AVAILABILITY"
AFTER INSERT OR UPDATE OF ISBN, PersonID, checkout_date, checkin_date
ON Checkouts
FOR EACH ROW
BEGIN
    IF :NEW.checkout_date = NULL
    THEN
        UPDATE Book
        SET available = 'N'
        WHERE ISBN IN (SELECT :NEW.ISBN FROM Checkouts);
    END IF;
END;

Here is an image of my ERD:

ERD

I have been looking into and double checking my trigger syntax, If condition syntax, subquery syntax, and googling this error but have found nothing that has helped. I am new to PL/SQL and would appreciate any help in understanding what I have done wrong or missed.

Upvotes: 0

Views: 189

Answers (1)

d r
d r

Reputation: 7786

  • PLS-00103: Encountered the symbol end-of-file error is SYNTAX ERROR
  • Copied your trigger and adjusted it to one of my test tables - it works. I removed NONEDITIONABLE and changed trigger table name as well as column names and table/column beeing updated by trigger.
    To Do:
  • Check your syntax again or write the trigger from scratch once more
  • "...WHERE ISBN IN (SELECT :NEW.ISBN FROM Checkouts)..." selects one fixed value (FOR EACH ROW) :NEW.ISBN of triggering table, better ->> "... WHERE ISBN = :NEW.ISBN ..."
  • Prety sure that you don't need NONEDITIONABLE trigger for your books tracking app...
    Regards...

Upvotes: 0

Related Questions