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 != NEW.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=suma_neto+val_neto,
suma_imp=suma_imp+val_imp,
total_con_imp=round(suma_neto+suma_imp)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=OLD.por_impto;
RAISE DEBUG ' -- ';
END IF;
-- Substract old value and add new value.
val_neto:=NEW.total_neto-OLD.total_neto;
val_imp:=NEW.valor_imp-OLD.valor_imp;
RAISE DEBUG 'Modify: net % tax % ', val_neto, val_imp;
ELSIF (TG_OP = 'INSERT') THEN
-- Add NEW value.
val_neto:=NEW.total_neto;
val_imp:=NEW.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=suma_neto + val_neto,
valor_imp=valor_imp + val_imp,
total_con_imp=round(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=NEW.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=suma_neto + val_neto,
valor_imp=valor_imp + val_imp,
total_con_imp=round(suma_neto + valor_imp, 2)
WHERE ventas_cab_id=NEW.ventas_cab_id AND
por_impto=NEW.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=0.
DELETE FROM ventas_imp
WHERE ventas_cab_id=id_ventas_cab AND
(total_con_imp=0 OR total_con_imp IS NULL);
RAISE DEBUG 'Delete records with amount=0 or null ';
-- I save the record_id of table ventas_imp and return the updated record.
NEW.total_con_imp := round(total_neto + valor_imp, 2); -- Update
calculations.
NEW.ventas_imp_id := id_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 (