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 ( -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql