Error in trigger function in PostgreSQL

Question

I have three tables in my database - Students, Books and Books2student.

Students table has StudentID, First_Name, Last_Name.
Books table has ISBN_no, name, no. of copies available.
Books2Students table which has StudentID, books issued, issue date, due date.

I want to create a trigger that when a student is deleted from books2students then no. of copies in Books table should be increased and if I insert a book in books2student then no of copies in books should be decreased.

I wrote a function and trigger, but I am getting error in the function

CREATE FUNCTION student_to_book()   RETURNS trigger AS  '
BEGIN
  IF tg_op = ''DELETE'' THEN
        UPDATE books 
        SET books.no_of_copies_available = no_of_copies_available+1
        WHERE Books2Students.Book_Issued=books.ISBN_no;

  END IF;
  IF tg_op = ''INSERT'' THEN
        UPDATE books  
        SET books.no_of_copies_available = no_of_copies_available-1
        WHERE Books2Students.Book_Issued=books.ISBN_no;   

  END IF;
END
' LANGUAGE plpgsql;

I get error an error:

ERROR: missing FROM-clause entry for table "books2students"
  Where: PL/pgSQL function student_to_book() line 10 at SQL statement

To refer to the row that is being inserted or deleted (for which the trigger has been fired), the syntax is `NEW.book_issued` or `OLD.book_issued` instead of `Books2Students.Book_Issued`. That's the reason of the error. See postgres documentation for more. — Daniel Vérité, Mar 19 '15 at 21:51

Erwin Brandstetter · Answer 1 · 2020-08-25T23:47:22.343

There are multiple issues. This could work:

CREATE OR REPLACE FUNCTION student_to_book()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF TG_OP = 'DELETE' THEN
      UPDATE books 
      SET    no_of_copies_available = no_of_copies_available + 1
      WHERE  ISBN_no = OLD.Book_Issued;

   ELSIF TG_OP = 'INSERT' THEN
      UPDATE books  
      SET    no_of_copies_available = no_of_copies_available - 1
      WHERE  ISBN_no = NEW.Book_Issued;
   END IF;
END
$func$;

That does not cover TG_OP = UPDATE, yet ...

Often, a MATERIALIZED VIEW is a better approach to keep track of a count per book.

score 0 · Answer 2 · answered Mar 19 '15 at 20:39

You are getting the error because you are referring to the table books2students in the WHERE clause but not having a FROM clause for it. I assume the trigger is for this table so you can use the keyword NEW in the update part and the keyword OLD in the delete part instead of the table name. Like this:

IF tg_op = ''INSERT'' THEN
UPDATE books
  SET books.no_of_copies_available = no_of_copies_available-1
  WHERE NEW.Book_Issued=books.ISBN_no;

Error in trigger function in PostgreSQL

2 Answers2

Linked