Create a procedure in PostGreSql

Question

Create a procedure in PostGreSql

Navigation

#1 by (1 votes)

1

I have created a database in PostGreSql of Equipment Allocation where one or many Equipment can be Allocated by one or many Sectors. That is, I have 3 tables in this relation: One is the Table Sector with 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 STATUS column 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)
)

Finally, the ALOCA Table (code, date of allocation, date of return) that relates to two previous ones:

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: What would be the Procedure that updates the Equipment STATUS to "ALLOCATED" after this same equipment has been linked to an Allocation?

postgresql stored-procedures trigger

asked by anonymous 04.09.2017 / 21:01

1 answer

Problems with javascript and google maps API Tables not being generated - Hibernate

score 1 · Accepted Answer

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();