Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL

Question

I have a database for a petshop for class that contains the table Animals with IDs and the dates of the last consult requisition for each animal, amongst other irrelevant columns and the table Requisition which contains the animals' IDs and requisition dates.

And I need to create a trigger that will update the date of the last consult requisition for a specific animal whenever a new requisition (containing the date and ID) is inserted on the table. I'm not very good at SQL yet, but here's what I have so far:

ALTER TABLE animal
ADD lastRequestDate date;
  
CREATE OR REPLACE FUNCTION addRequestDate () 
RETURNS trigger as $$
BEGIN 
    UPDATE animal JOIN solicita ON idanimal
    SET lastRequestDate  -- here's where I got lost !!

END;
$$ language plpgsql;
  
CREATE TRIGGER updateLastRequest
AFTER INSERT ON request
FOR EACH ROW
EXECUTE PROCEDURE addRequestDate();

score 0 · Answer 1 · answered Jun 14 '21 at 02:07

I rewrite the function for you. Could you please check?

CREATE OR REPLACE FUNCTION addRequestDate RETURNS TRIGGER as
$BODY$
BEGIN
    UPDATE
           animal
           SET    lastRequestDate = now() -- This updates the last req date to todays date.
           FROM solicita
           WHERE animal.idanimal = solicita.idanimal
END;
$BODY$
language plpgsql;

Basically we have to update animal table with todays date whenever someone adds a new row in solicita table.

score 0 · Accepted Answer · answered Jun 14 '21 at 03:36

Just refer to the special row variable NEW referring to the inserted row of the triggering table:

CREATE OR REPLACE FUNCTION udpate_request_date()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE animal
   SET    last_request_date = now()
   WHERE  animal_id = NEW.animal_id;
END
$func$;

CREATE TRIGGER request_after_insert
AFTER INSERT ON request
FOR EACH ROW
EXECUTE FUNCTION udpate_request_date();

And use proper, legal, lower-case names to make your life easier. See:

Are PostgreSQL column names case-sensitive?

Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL

2 Answers2