TRIGGER PostgresSQL, insert and delete

Question

TRIGGER PostgresSQL, insert and delete

Navigation

#1 by (0 votes)

0

I have 3 tables, one "task" and two other identical tables, with their respective fields of name "imputa_pendientes" and "imputa". The 3 tables have a common field project_id.

I want to ensure that when a line is inserted in tasks with id_secci = 6, the lines containing that project_id of the line inserted in tasks are copied from the table "imputa_pendientes" to the table "imputa".

Here the tables with their columns:

CREATE TABLE tar.tarea
(
  id serial NOT NULL,
  id_proyecto integer NOT NULL,
  id_empleado_solicitante integer,
  id_empleado integer NOT NULL,
  id_secci integer NOT NULL,
  id_estado integer NOT NULL DEFAULT 1,
  semana date,
  fecha_entrega date,
  observaciones text,
  observaciones_entrega text,
  id_proto_periodo integer,
  revisada boolean NOT NULL DEFAULT false,

)
WITH (
  OIDS=FALSE
);
ALTER TABLE tar.tarea
  OWNER TO postgres;
GRANT ALL ON TABLE tar.tarea TO postgres;
GRANT ALL ON TABLE tar.tarea TO public;


Tabla pro.imputa_pendiente;

CREATE TABLE pro.imputa_pendiente
(
  id serial NOT NULL,
  insert_user_id oid,
  insert_fecha date,
  update_user_id oid,
  update_fecha date,
  id_proyecto integer,
  importe numeric(12,2),
  observaciones text,
  CONSTRAINT imputa_pkey PRIMARY KEY (id),
  CONSTRAINT imputa_id_proyecto_fkey FOREIGN KEY (id_proyecto)
      REFERENCES pro.proyecto (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE pro.imputa_pendiente
  OWNER TO postgres;
GRANT ALL ON TABLE pro.imputa_pendiente TO postgres;
GRANT ALL ON TABLE pro.imputa_pendiente TO public;


TABLA pro.imputa;

CREATE TABLE pro.imputa
(
  id serial NOT NULL,
  insert_user_id oid,
  insert_fecha date,
  update_user_id oid,
  update_fecha date,
  id_proyecto integer,
  importe numeric(12,2),
  observaciones text,
  CONSTRAINT imputa_pkey PRIMARY KEY (id),
  CONSTRAINT imputa_id_proyecto_fkey FOREIGN KEY (id_proyecto)
      REFERENCES pro.proyecto (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE pro.imputa
  OWNER TO postgres;
GRANT ALL ON TABLE pro.imputa TO postgres;
GRANT ALL ON TABLE pro.imputa TO public;

And I created this function, but, I do not copy the lines of that project_id of imputa_pendiente to imputa if there is more than one line in imputa_pendiente with that project_id. In one of the attempts, he has copied me well the values, but only having a line in imputa_pendiente.

Here the function:

--DROP FUNCTION transladar_pendiente_imputado();
CREATE OR REPLACE FUNCTION transladar_pendiente_imputado() RETURNS "trigger" AS $$
DECLARE
_id INTEGER;
_insert_user_id INTEGER;
_insert_fecha DATE;
_update_user_id INTEGER;
_update_fecha DATE;
_id_proyecto INTEGER;
_importe NUMERIC;
_observaciones VARCHAR;
BEGIN
    IF (tg_op = 'INSERT') THEN
        _id := NEW.id;
        _id_proyecto := NEW.id_proyecto;

    END IF;

    IF (NEW.id_secci= 6 ) THEN

            _insert_user_id := (SELECT insert_user_id FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);
            _insert_fecha := (SELECT insert_fecha FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);
            _update_user_id := (SELECT update_user_id FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);
            _update_fecha := (SELECT update_fecha FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);
            _importe := (SELECT importe FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);
            _observaciones := (SELECT observaciones FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto);

            INSERT INTO pro.imputa(id, insert_user_id, insert_fecha, update_user_id, update_fecha, id_proyecto, importe, observaciones)
            VALUES (_id, _insert_user_id, _insert_fecha, _update_user_id, _update_fecha, _id_proyecto, _importe, _observaciones);

            DELETE FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto; 
        RETURN NEW;

    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql; COMMENT ON FUNCTION transladar_pendiente_imputado() IS 'Copia los registros de imputado_pendiente a imputado del id_proyecto de la tarea pedidos id_secci=6';


--DROP TRIGGER transladar_pendiente;
CREATE TRIGGER transladar_pendiente
    AFTER INSERT ON tar.tarea
    FOR EACH ROW 
    EXECUTE PROCEDURE transladar_pendiente_imputado();

If I insert a task with id_secci = 6 (when imputa_pendiente has several lines with that project_id)

ERROR:  more than one row returned by a subquery used as an expression
CONTEXT:  SQL statement "SELECT (SELECT insert_user_id FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto)"
PL/pgSQL function transladar_pendiente_imputado() line 20 at assignment

INSERT INTO tar.tarea_con_proyecto__vista (id_proyecto, revisada, semana, id_estado, id_secci, id_empleado_solicitante, id_empleado) VALUES ('2471', FALSE, '23/07/2018', '1', '6', '240', '240')
ERROR:  more than one row returned by a subquery used as an expression
CONTEXT:  SQL statement "SELECT (SELECT insert_user_id FROM pro.imputa_pendiente WHERE id_proyecto = _id_proyecto)"
PL/pgSQL function transladar_pendiente_imputado() line 20 at assignment

Can someone help me? Thanks

postgresql trigger

asked by user3158502 24.07.2018 в 12:29

source

1 answer

Restart counter to 1 in php Insert order detail in laravel

score 0 · Accepted Answer

To make the trigger run only when a record with id_secci = 6 is inserted:

CREATE TRIGGER trasladar_pendiente 
  AFTER INSERT ON tar.tarea 
  FOR EACH ROW WHEN (NEW.id_secci = 6) EXECUTE PROCEDURE trasladar_pendiente_imputado();

And the function:

CREATE OR REPLACE FUNCTION trasladar_pendiente_imputado() RETURNS trigger AS $$
BEGIN
  INSERT INTO pro.imputa (id, insert_user_id, insert_fecha, update_user_id, update_fecha, id_proyecto, importe, observaciones)
   SELECT NEW.id, insert_user_id, insert_fecha, update_user_id, update_user_id, update_fecha, NEW.id_proyecto, importe, observaciones 
   FROM pro.imputa_pendiente WHERE id_proyecto = NEW.id_proyecto;

  DELETE FROM pro.imputa_pendiente WHERE id_proyecto = NEW.id_proyecto;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The problem that you show in the publication may be because the table pro.imputa_pendiente contains more than one record with the id_proyecto , which causes, when assigning the result of the query to a variable, the specified error Make sure the data is correct.

In the case that it is necessary to have multiple records with the same id_proyecto in the table pro.imputa_pendiente you can add LIMIT 1 to the specified query, in such a way that only one element is selected. This, depending on the type of application or orientation of your database design, may or may not be feasible.