Update trigger in PLpgsql

Question

I am totally new to sql. Please, can anybody help me to create a trigger to PostgreSQL database. So i have 1 table named "tables" with 2 columns named "w" (games won, integer) and "gp" (games played, integer). So the plot is auto-update trigger of "gp" value depending on modifying "w" value.

Table looks like this:

club | gp | w |

Juventus| 2 | 2 |

Lazio | 2 | 2 |

I need to create an update trigger which will update "gp" column like gp+1 after adding 1 won game to column "w" like w+1. What it would be?

I've tried something like this:

CREATE OR REPLACE FUNCTION fn_gp_increment()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.w = OLD.w+1 THEN
        UPDATE tables SET NEW.gp = OLD.gp+1;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER gp_increment
BEFORE UPDATE OF w ON tables
FOR EACH ROW
EXECUTE FUNCTION n_gp_increment();

Unfortunately, it doesn't work. I think i am going wrong way and it seems to me that i don't understand some basics about triggers. Will be grateful for any help with this. Thanks for your attention.

Answer 1

Step 1: Create the Function

CREATE OR REPLACE FUNCTION update_gp()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the w (games won) column is being incremented
    IF NEW.w > OLD.w THEN
        -- Increment the gp (games played) column by the same amount
        NEW.gp := OLD.gp + (NEW.w - OLD.w);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 2: Create the Trigger

CREATE TRIGGER update_gp_trigger
BEFORE UPDATE OF w ON tables
FOR EACH ROW
WHEN (OLD.w IS DISTINCT FROM NEW.w)
EXECUTE FUNCTION update_gp();

Explanation: Function update_gp(): This function checks if the w column has increased. If it has, it updates the gp column accordingly. Trigger update_gp_trigger: This trigger calls the function before any update to the w column. The WHEN clause ensures that the trigger only fires if the value of w has actually changed.

UPDATE tables
SET w = w + 1
WHERE club = 'Juventus';