Create a trigger that updates the columns of another table (Postgresql)

Question

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

bookOrder

orderID	book name	required	availability
1	Harry Potter	9	yes
2	Twilight	8	yes
3	Bible	8	yes

books

book name	quantity
Harry Potter	10
Twilight	5
Bible	8

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;

Why do I get an error at the last line where the ($$) is at?

Are you using psql or some other client to create the trigger function? Not all SQL clients support Postgres' dollar quoting. — user330315
– user330315, Commented Dec 4, 2021 at 9:58
Unrelated, but: you don't need the FROM in your UPDATE statement: WHERE bo.bookname = new.bookname; would be enough. — user330315
– user330315, Commented Dec 4, 2021 at 9:58

LukStorms · Accepted Answer · 2021-12-04 11:28:34Z

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

Thanks! That worked really well. I see how yours is a lot more efficient than mine, I'll have to keep that in mind, Cheers!

Collectives™ on Stack Overflow

Create a trigger that updates the columns of another table (Postgresql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related