Postgresql – Trouble when accessing old after deleting a row in postgrsql

functionspostgresqltrigger

When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database.
When I run my code "addBalance" becomes Null and therefore all the sums become Null but when I run the query outside the function it runs exactly as I want it to. It looks like "old" is causing trouble. Any help is appreciated, thank you.

Here is the code and the tables are below the code.

CREATE OR REPLACE FUNCTION divideMoney()
RETURNS TRIGGER
AS $$
DECLARE athleteSum int;
DECLARE peopleCount bigint;
DECLARE addBalance int;
BEGIN
    SELECT sum::INTEGER from People P JOIN Accounts A ON P.ID = A.peopleID
    WHERE A.peopleID = old.ID INTO athleteSum;
    SELECT COUNT(*) FROM Accounts INTO peopleCount;
    RAISE NOTICE 'athleteSum --->%', athleteSum;
    RAISE NOTICE 'peopleCount --->%', peopleCount;
    addBalance := athleteSum / peopleCount::INTEGER;
    RAISE NOTICE 'addBalance --->%', addBalance;
    RAISE EXCEPTION 'STOP';
    FOR person IN 0..(peopleCount - 1) LOOP
        IF person <> old.ID THEN
            UPDATE Accounts
            SET sum = sum::INTEGER + addBalance
            WHERE peopleID = person;
        END IF;
    END LOOP;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER divedeDeletedMoney
BEFORE DELETE ON People
FOR EACH ROW
EXECUTE PROCEDURE divideMoney();


CREATE TABLE Gender (
       gender CHAR(1),
       description VARCHAR(10),
       PRIMARY KEY (gender)
);

CREATE TABLE People (
       ID INT,
       name VARCHAR(50),
       gender CHAR(1),
       height FLOAT,
       PRIMARY KEY (ID),
       FOREIGN KEY (gender) REFERENCES Gender (gender)
);



CREATE TABLE Accounts (
    peopleID int PRIMARY KEY,
    sum varchar(255),
    last_updated date,
    FOREIGN KEY (peopleID) REFERENCES People (id)
);

Best Answer

To have a realistic demo case, you need to populate the tables, not just create them.

Your title says "after deleting a row", but your trigger is clearly a "before" trigger.

If you (attempt to) delete a row from people which has no corresponding entry in accounts, then athleteSum naturally becomes NULL. What else would it do? If you wish to avoid this, you need to take steps to avoid it. Convert to zero? Throw an error? I don't know, only you can know.

addBalance := ROUND(athleteSum / peopleCount::INTEGER);

You are dividing two integers. The result of integer division is already an integer. No rounding is necessary. Shouldn't it be (peopleCount-1)? Otherwise you are distributing the value back to the row that was just (attempted to be) deleted.

FOR person IN 0..peopleCount LOOP

WHERE peopleID = person;

Why would you expect the peopleID to be densely packed between 0 and peopleCount? Especially since you just attempted to delete one of those intervening values?

RETURN NEW;

For a delete trigger, NEW is NULL. Returning NULL prevents the delete from actually happening. Is that what you want?

Related Question