From: | schaefer(at)alphanet(dot)ch |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Some additional PostgreSQL questions |
Date: | 2002-05-28 07:40:48 |
Message-ID: | Pine.LNX.3.96.1020528092524.15151A-100000@defian.alphanet.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sandeep Chibber | 2002-05-28 09:11:02 | Problem with the result set of postgres |
Previous Message | Stephan Szabo | 2002-05-28 06:51:51 | Re: Triggers and System Tables. |