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
PROCEDUREf_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
ina session   would fail.
 

PS/2: I am now using 7.2.1.