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:
x
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();
Advertisement
Answer
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: