error to do with trigger function and insert statement sql

Im trying to complete this question:

Add an update to the support ticket from a given staff member. If the ticket is closed no more updates should be allowed by either customers or staff members.

However im getting an error,ive put my code and the error output down below. Any help would be appreciated.

create or replace function update_ticket()
returns trigger 
language plpgsql
as $$
begin
if old.status = 'closed' then 
   RAISE EXCEPTION 'Ticket is closed';
end if;
return new;
end;
$$; 

CREATE TRIGGER update_trigger AFTER UPDATE OR INSERT ON coursework.TicketUpdate
FOR EACH ROW EXECUTE PROCEDURE update_ticket();

--Insert an update to a ticket with values: ticketUpdateID = 10050;
--Message ='Highlight folder for backup and press to trash or right click and choose delete'; -- 
--TicketID = 1010 ; StaffID=2

INSERT INTO TicketUpdate(ticketUpdateID, message, ticketID, StaffID)

VALUES (10050, 'Highlight folder for backup and press to trash or right click and choose delete', 1010, 2);

ERROR: record "old" has no field "status"

CONTEXT: SQL statement "SELECT old.status = 'closed'"

PL/pgSQL function public.update_ticket() line 3 at IF

SQL state: 42703

1 answer

  • answered 2021-05-13 22:12 BossGman

    Your trigger applies on INSERT but there is only and old row on UPDATE or DELETE see docs https://www.postgresql.org/docs/12/plpgsql-trigger.html.

    Also it wasn’t clear that the table your are inserting into has a status column if it does then your trigger should be ok for updates and you will need different logic for inserts.