A Rigger that checks the deadline for all products in the table

Asked

Viewed 88 times

0

Good afternoon to everyone in the community. I’m trying to make a Rigger that checks the products that have their expiration date and change their status in the table using postgresql

postgresql postgresql-administration

1 answer

1

Create the Trigger:

-- Function: public.produto_vencido()

-- DROP FUNCTION public.produto_vencido();

CREATE OR REPLACE FUNCTION public.produto_vencido()
  RETURNS trigger AS
$BODY$
BEGIN
 UPDATE public.produto SET ativo = FALSE WHERE validade < NOW()::DATE;
 RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.produto_vencido()
  OWNER TO postgres;

Add Trigger to the table:

-- Trigger: validade on public.produto

-- DROP TRIGGER validade ON public.produto;

CREATE TRIGGER validade
  BEFORE UPDATE OF nome, validade, id_produto
  ON public.produto
  FOR EACH ROW
  EXECUTE PROCEDURE public.produto_vencido();

  • Okay, but this way Rigger will only fire when I update the right table ? And if this was like a cycle, it would always scan and modify those products that have passed ?

  • In the first sale of the day will check your table, I do not think it necessary to be checked continuously, because it will only change the condition at the turn of the day, I suggest to make a process of opening the day.

Browser other questions tagged postgresql postgresql-administration

You are not signed in. Login or sign up in order to post.