Re: Some additional PostgreSQL questions

On Mon, 27 May 2002, Marc SCHAEFER wrote:

> > compta=> DELETE FROM ecriture WHERE id = 1;
> > DELETE 0
>
> > The funny thing is the DELETE not saying an error, but not deleting (which
> > is good, but I would like an error).
>
> Then make your trigger raise an error. Returning NULL out of the

It does:

CREATE TRIGGER t_ecriture_balance_insert
AFTER INSERT OR UPDATE OR DELETE
ON ecriture
FOR EACH ROW
EXECUTE PROCEDURE f_ecriture_balance_check ();

CREATE FUNCTION f_ecriture_balance_check ()
RETURNS opaque
AS 'DECLARE amount NUMERIC(10, 2);
BEGIN
amount := 0;
SELECT SUM(montant_signe)
FROM ecriture
WHERE (lot = NEW.lot)
INTO amount;

IF (amount != 0) THEN
RAISE EXCEPTION ''Sum of ecritures in lot % is not zero but %'',
NEW.lot,
amount;
END IF;

RETURN new;
END;'
LANGUAGE 'plpgsql';

which works, at least in some cases:

compta=> UPDATE ecriture SET montant_signe = -249 WHERE id = 1;
ERROR: Sum of ecritures in lot 1 is not zero but 1.00
compta=> UPDATE ecriture SET montant_signe = -250 WHERE id = 1;
UPDATE 1
compta=> DELETE FROM ecriture WHERE id = 1;
DELETE 0

now, removing an ecriture should make the balance unbalanced, and as it's
run AFTER should detect the problem, no ?

I tried to modify it slightly so to use OLD instead of NEW:

CREATE TRIGGER t_ecriture_balance_delete
AFTER DELETE
ON ecriture
FOR EACH ROW
EXECUTE PROCEDURE f_ecriture_balance_check_delete ();

however it wasn't better.

PS: the second problem is solved thanks to your help, including
my initial problem (question 3) where two transaction in a session
would fail.

PS/2: I am now using 7.2.1.