Thread: Control reached end of trigger procedure without RETURN
Buenos días: Tengo una función que se dispara en un trigger cuya finalidad es; crear un registro en otra tabla o actualizarlo y devolviendoel id del registro creado/modificado para guardarlo en la tabla desde la que se llama. Intentaré explicarlo mejor: tablaA --> Es la que dispara el trigger y en la que quiero guardar el id del registro modificado / creado en la tabla B. tablaB --> Digamos que es una tabla resumen de la tablaA y se mantiene desde el trigger de la misma, creando modificandoy/o borrando los registros. Finalidad --> Eso, actualizar la tablaB desde la A y guardar en el registro de de la tablaA a que registro de la tablaB "pertenece". Cuando hago el insert me devuelve el error del asunto Control reached end of trigger procedure without RETURN y me indica la línea correspondiente al INSERT. ¿Acaso no continúa el flujo if (not found) y debería hacer el return NEW tras el insert? ¿y lo mismo en el update del exception? Agradezco de antemano vuestro tiempo y ayudas. Un saludo. P.D. Estructura de la función. DECLAREid_tablaB integer; -- Id de la tabla B. BEGINIF (TG_OP = 'DELETE') THEN -- Descontar valores ELSIF (TG_OP = 'UPDATE') THEN -- Modificar valores. ELSIF (TG_OP = 'INSERT') THEN -- Asignar valores END IF; -- Update con nuevos valores y retornamos el id para actualizar detalle.UPDATE tablaBSET campo1=valor1, campo2=valor2WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; -- Si no existe, hacemos el insert y retornamos el id para actualizar detalle.IF (NOT FOUND) THEN BEGIN -- No existÌael registro y lo creamos. -- -- AQUÍ DEVUELVEL EL ERROR. -- INSERT INTO tablaB ( campo1, campo2) VALUES ( valor1, valor2 ) RETURNING tablaB_id INTO id_tablaB; EXCEPTION WHEN UNIQUE_VIOLATION THEN UPDATE tablaB SET campo1=valor1, campo2=valor2 WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; END;END IF; -- Borrado de ventas_imp si el resumen del % y factura es 0.DELETE FROM tablaB WHERE tablaA_id=id_tablaA AND (campo3=0OR campo3 IS NULL); -- Guardamos el id de la tabla ventas_imp y retornamos el record modificado.NEW.tablaB_id := id_tablaBreturn NEW; END
El 24/01/2011 19:19, Andrej escribió: > And, since the list is in English - please translate? I'm sorry, I started writing and the language ... :-( I have a function that is triggered in a trigger whose purpose is, create a record in another table or update and return the id of the record created / modified to save in the table from being called. Try to explain it better: TableA -> Launch the trigger and I want to save the modified record id / created in Table B. TableB -> Is a summary table of TableA and is updated from TableA trigger. Purpose -> Update TableB from TableA and store in the record of TableA the Id of record TableB. When I launch one insert returns error: I put the function and the returned error. Thanks in advance, Best regards. *********** CODE *********** CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp() RETURNS trigger AS $BODY$ DECLARE id_ventas_imp integer; -- Taxes Id. id_ventas_cab integer; -- Bill Id. val_neto numeric(19,4); -- net value. val_imp numeric(19,4); -- tax value. BEGIN -- IF (TG_OP =DELETE') THEN -- Descontar valor old.. val_neto:=1 * OLD.total_neto; val_imp:=1 * OLD.valor_imp; RAISE DEBUG 'Deleted: net % tax % ', val_neto, val_imp; ELSIF (TG_OP =UPDATE') THEN -- If you change the tax, substract the amount value. IF ((OLD.por_impto !=EW.por_impto) AND (OLD.por_impto IS NOT NULL) AND (NEW.ventas_cab_id IS NOT NULL)) THEN RAISE DEBUG '--'; -- Substract the od value. val_neto:=1 * OLD.total_neto; val_imp:=1 * OLD.valor_imp; UPDATE ventas_imp SET suma_neto=ma_neto+val_neto, suma_imp=ma_imp+val_imp, total_con_imp=und(suma_neto+suma_imp) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=D.por_impto; RAISE DEBUG ' -- '; END IF; -- Substract old value and add new value. val_neto:=W.total_neto-OLD.total_neto; val_imp:=W.valor_imp-OLD.valor_imp; RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp; ELSIF (TG_OP =INSERT') THEN -- Add NEW value. val_neto:=W.total_neto; val_imp:=W.valor_imp; RAISE INFO 'New: net % tax % ', val_neto, val_imp; END IF; -- update with new values and returning it the "id" of the updated record . UPDATE ventas_imp SET suma_neto=ma_neto + val_neto, valor_imp=lor_imp + val_imp, total_con_imp=und(suma_neto + valor_imp, 2) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=W.por_impto RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Update done in record_id % ', id_ventas_imp; -- If not found, insert new record and return "id". IF (NOT FOUND) THEN BEGIN -- . INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( NEW.ventas_cab_id, NEW.por_impto, val_neto, val_imp, round(val_neto+val_imp, 2) ) RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Not found. New record =% ', id_ventas_imp; EXCEPTION -- Two transactions trying to create the record. WHEN UNIQUE_VIOLATION THEN UPDATE ventas_imp SET suma_neto=ma_neto + val_neto, valor_imp=lor_imp + val_imp, total_con_imp=und(suma_neto + valor_imp, 2) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=W.por_impto RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Two transactions trying to create the record: I launch update in record_id % ', id_ventas_imp; END; END IF; -- Delete records with amount= DELETE FROM ventas_imp WHERE ventas_cab_id=_ventas_cab AND (total_con_imp=OR total_con_imp IS NULL); RAISE DEBUG 'Delete records with amount=or null '; -- I save the record_id of table ventas_imp and return the updated record. NEW.total_con_imp :=ound(total_neto + valor_imp, 2); -- Update calculations. NEW.ventas_imp_id :=d_ventas_imp; RAISE DEBUG 'New of record values: Total with tax = Record Id of tax table=, NEW.total_con_imp, NEW.ventas_imp_id; return NEW; END $BODY$ LANGUAGE 'plpgsql' VOLATILE ############################## INFO: Nuevo: neto 5.0000 impuesto 0.5000 ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab" SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( $1 , $2 , $3 , $4 , round( $3 + $4 , 2) ) RETURNING ventas_imp_id" PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement ********** Error ********** ERROR: control reached end of trigger procedure without RETURN Estado SQL:2F005 Contexto:PL/pgSQL function "ventas_imp_a_ventas_cab" SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( $1 , $2 , $3 , $4 , round( $3 + $4 , 2) ) RETURNING ventas_imp_id" PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement ############################## the #56 line is the INSERT instruction in the block IF (NOT FOUND) THEN BEGIN -- INSERT INTO ventas_imp (
El 24/01/2011 19:19, Andrej escribió: > And, since the list is in English - please translate? I'm sorry, I started writing and the language ... :-( I have a function that is triggered in a trigger whose purpose is, create a record in another table or update and return the id of the record created / modified to save in the table from being called. Try to explain it better: TableA -> Launch the trigger and I want to save the modified record id / created in Table B. TableB -> Is a summary table of TableA and is updated from TableA trigger. Purpose -> Update TableB from TableA and store in the record of TableA the Id of record TableB. When I launch one insert returns error: I put the function and the returned error. Thanks in advance, Best regards. *********** CODE *********** CREATE OR REPLACE FUNCTION ventas_det_a_ventas_imp() RETURNS trigger AS $BODY$ DECLARE id_ventas_imp integer; -- Taxes Id. id_ventas_cab integer; -- Bill Id. val_neto numeric(19,4); -- net value. val_imp numeric(19,4); -- tax value. BEGIN -- IF (TG_OP =DELETE') THEN -- Descontar valor old.. val_neto:=1 * OLD.total_neto; val_imp:=1 * OLD.valor_imp; RAISE DEBUG 'Deleted: net % tax % ', val_neto, val_imp; ELSIF (TG_OP =UPDATE') THEN -- If you change the tax, substract the amount value. IF ((OLD.por_impto !=EW.por_impto) AND (OLD.por_impto IS NOT NULL) AND (NEW.ventas_cab_id IS NOT NULL)) THEN RAISE DEBUG '--'; -- Substract the od value. val_neto:=1 * OLD.total_neto; val_imp:=1 * OLD.valor_imp; UPDATE ventas_imp SET suma_neto=ma_neto+val_neto, suma_imp=ma_imp+val_imp, total_con_imp=und(suma_neto+suma_imp) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=D.por_impto; RAISE DEBUG ' -- '; END IF; -- Substract old value and add new value. val_neto:=W.total_neto-OLD.total_neto; val_imp:=W.valor_imp-OLD.valor_imp; RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp; ELSIF (TG_OP =INSERT') THEN -- Add NEW value. val_neto:=W.total_neto; val_imp:=W.valor_imp; RAISE INFO 'New: net % tax % ', val_neto, val_imp; END IF; -- update with new values and returning it the "id" of the updated record . UPDATE ventas_imp SET suma_neto=ma_neto + val_neto, valor_imp=lor_imp + val_imp, total_con_imp=und(suma_neto + valor_imp, 2) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=W.por_impto RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Update done in record_id % ', id_ventas_imp; -- If not found, insert new record and return "id". IF (NOT FOUND) THEN BEGIN -- . INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( NEW.ventas_cab_id, NEW.por_impto, val_neto, val_imp, round(val_neto+val_imp, 2) ) RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Not found. New record =% ', id_ventas_imp; EXCEPTION -- Two transactions trying to create the record. WHEN UNIQUE_VIOLATION THEN UPDATE ventas_imp SET suma_neto=ma_neto + val_neto, valor_imp=lor_imp + val_imp, total_con_imp=und(suma_neto + valor_imp, 2) WHERE ventas_cab_id=W.ventas_cab_id AND por_impto=W.por_impto RETURNING ventas_imp_id INTO id_ventas_imp; RAISE DEBUG 'Two transactions trying to create the record: I launch update in record_id % ', id_ventas_imp; END; END IF; -- Delete records with amount= DELETE FROM ventas_imp WHERE ventas_cab_id=_ventas_cab AND (total_con_imp=OR total_con_imp IS NULL); RAISE DEBUG 'Delete records with amount=or null '; -- I save the record_id of table ventas_imp and return the updated record. NEW.total_con_imp :=ound(total_neto + valor_imp, 2); -- Update calculations. NEW.ventas_imp_id :=d_ventas_imp; RAISE DEBUG 'New of record values: Total with tax = Record Id of tax table=, NEW.total_con_imp, NEW.ventas_imp_id; return NEW; END $BODY$ LANGUAGE 'plpgsql' VOLATILE ############################## INFO: Nuevo: neto 5.0000 impuesto 0.5000 ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function "ventas_imp_a_ventas_cab" SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( $1 , $2 , $3 , $4 , round( $3 + $4 , 2) ) RETURNING ventas_imp_id" PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement ********** Error ********** ERROR: control reached end of trigger procedure without RETURN Estado SQL:2F005 Contexto:PL/pgSQL function "ventas_imp_a_ventas_cab" SQL statement "INSERT INTO ventas_imp ( ventas_cab_id, por_impto, suma_neto, valor_imp, total_con_imp) VALUES ( $1 , $2 , $3 , $4 , round( $3 + $4 , 2) ) RETURNING ventas_imp_id" PL/pgSQL function "ventas_det_a_ventas_imp" line 56 at SQL statement ############################## the #56 line is the INSERT instruction in the block IF (NOT FOUND) THEN BEGIN -- INSERT INTO ventas_imp (