Create a precedent in Postgresql

Question

I have created a database in Equipment Allocation Postgresql where one or many Equipments can be Allocated by one or many Sectors. That is, I have 3 tables in this relation: One is the Sector Table with the columns (CODE AND NAME) :

CREATE TABLE public.setor
(
  setcod serial NOT NULL,
  setnome character varying(200) NOT NULL,
  CONSTRAINT pk_setor PRIMARY KEY (setcod)
)

The second is Equipment with columns (CODE, NAME and STATUS), this column STATUS by default in the act of registering an equipment becomes "NOT ALLOCATED":

CREATE TABLE public.equipamento
(
  eqcod SERIAL NOT NULL,
  ednome character varying(200) NOT NULL,
  edstatus character varying(30) NOT NULL DEFAULT 'NÃO ALOCADO',
  CONSTRAINT pk_equipamento PRIMARY KEY (eqcod)
)

And finally the ALOCA Table (code, allocation date, return date) which relates to two previous:

CREATE TABLE public.aloca
(
  alocod integer NOT NULL,
  alodtdevolucao date NOT NULL,
  alodtalocacao date NOT NULL,
  alo_eqcod integer NOT NULL,
  alo_setcod integer NOT NULL,
  alo_funcod integer NOT NULL,
  CONSTRAINT pk_aloca PRIMARY KEY (alocod),
  CONSTRAINT fk_equipamento FOREIGN KEY (alo_eqcod)
      REFERENCES public.equipamento (eqcod) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_funcionario FOREIGN KEY (alo_funcod)
      REFERENCES public.funcionario (funcod) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_setor FOREIGN KEY (alo_setcod)
      REFERENCES public.setor (setcod) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

QUESTION: How would the Procedure that updates the Equipment STATUS to "ALLOCATED" after this same equipment is linked to an Allocation?

Answer 1

I think this might help you.

First you create the function:

CREATE OR REPLACE FUNCTION aloca_equip()
RETURNS trigger AS $teste_trigger$
BEGIN
UPDATE equipamento SET edstatus = 'ALOCADO' WHERE eqcod = NEW.alo_eqcod;
RETURN NEW;
END;
$teste_trigger$ LANGUAGE plpgsql; 

Then the Trigger:

CREATE TRIGGER trigger_aloca_equip
AFTER INSERT ON aloca
FOR EACH ROW
EXECUTE PROCEDURE aloca_equip();