So I have two tables "books" and "bookOrder" and they look something like this:

bookOrder

books

I want to create a trigger that every time the books table is updated, it will update the bookorder availability column based on the book quantity column.

I have created a query as follows that does what I need:

UPDATE bookOrder bo
SET avalability = CASE WHEN b.quantity < bo.required THEN 'NO' ELSE 'YES' END
FROM books b
WHERE b.bookName = bo.bookName

However, would be better if this was automatic so a trigger was my first thought. To create a trigger, I would need a function to execute. This is where I have been trouble creating the function. My attempt is as below:

CREATE OR REPLACE FUNCTION update_books() RETURNS TRIGGER AS $$
    BEGIN
        UPDATE bookOrder bo
        SET avalability = CASE WHEN b.quantity < bo.required THEN 'NO' ELSE 'YES' END
        FROM books b
        WHERE b.bookName = bo.bookName
    END
$$  LANGUAGE plpgsql;

I am getting a error at the last line where the ($$) is at. Unsure why. Not even sure if this is the correct formatting to create the trigger.

Thanks in advanced!

CodePudding user response：

The procedure compiled after adding a semi-colon after the WHERE clause.
And it also had to return something.

But the update doesn't need to update each row in the target table whenever books gets updated.

CREATE OR REPLACE FUNCTION update_bookorder_availability() 
RETURNS TRIGGER
AS $$
BEGIN

  UPDATE bookOrder bo
  SET availability = CASE 
                     WHEN bo.required <= NEW.quantity THEN 'yes' 
                     ELSE 'no' 
                     END
  WHERE bo.bookName = NEW.bookName;
  
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER bookorder_availability_changes
  AFTER UPDATE
  ON books
  FOR EACH ROW
  EXECUTE PROCEDURE update_bookorder_availability();

-- before update
select * from books;
select * from bookOrder;

bookname     | quantity
:----------- | -------:
Harry Potter |       10
Twilight     |        5
Bible        |        8

orderid | bookname     | required | availability
------: | :----------- | -------: | :-----------
      1 | Harry Potter |        9 | yes         
      2 | Twilight     |        8 | yes         
      3 | Bible        |        8 | yes         

update books set quantity = quantity;

3 rows affected

-- after update
select * from books;
select * from bookOrder;

bookname     | quantity
:----------- | -------:
Harry Potter |       10
Twilight     |        5
Bible        |        8

orderid | bookname     | required | availability
------: | :----------- | -------: | :-----------
      1 | Harry Potter |        9 | yes         
      2 | Twilight     |        8 | no          
      3 | Bible        |        8 | yes         

Test on db<>fiddle here