current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

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
share|improve this question
    
"I am getting error": What error might that be? – Mike Sherrill 'Cat Recall' Mar 19 at 18:57
    
Missing LANGUAGE plpgsql clause. Other looks well. If somebody can help to you, you have to show a error message. – Pavel Stehule Mar 19 at 18:57
    
ERROR: missing FROM-clause entry for tab le "books2students" Where: PL/pgSQL function student_to_book() line 10 at SQL statement – John Mar 19 at 20:16
    
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 at 21:51
    
So do you have your answer? – Erwin Brandstetter Jul 24 at 3:19

2 Answers 2

up vote 0 down vote

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;
share|improve this answer
up vote 0 down vote

There are multiple issues. This could work:

CREATE OR REPLACE FUNCTION student_to_book()
  RETURNS trigger 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$ LANGUAGE plpgsql;

Start by reading the manual about the special records OLD and NEW here.

Then read the manual about UPDATE here.

And you did not cover TG_OP = UPDATE, yet.

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.