CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsql
Ryan Haslag
Ryan Haslag

Reputation: 33

SQL trigger not working

CREATE TABLE lab7.standings
(
    team_name    VARCHAR(100) NOT NULL PRIMARY KEY,
    wins         INTEGER,
    losses       INTEGER,
    winPct       NUMERIC,
    CHECK(wins > 0),
    CHECK(losses >0)
);

CREATE OR REPLACE FUNCTION
calc_winning_percentage()
RETURNS trigger AS $$
BEGIN
    New.winPct := New.wins /(New.wins + New.losses);
    RETURN NEW;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER
update_winning_percentage
AFTER INSERT OR UPDATE ON standings
FOR EACH ROW EXECUTE PROCEDURE calc_winning_percentage();

This is accurately updating the wins in my standings table, but doesn't seem to send my new calculated winning percentage.

Upvotes: 1

Views: 169

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

In addition to changing the trigger to BEFORE like pointed out by @Grijesh:

I notice three things in your table definition:

1.integer vs. numeric

wins and losses are of type integer, but winPct is numeric.

Try the following:

SELECT 1 / 4, 2 / 4

Gives you 0 both times. The result is of type integer, fractional digits are truncated towards zero. This happens in your trigger function before the integer result is coerced to numeric in the assignment. Therefore, changes in wins and losses that only affect fractional digits are lost to the result. Fix this by:

.. either changing the column definition to numeric for all involved columns in the base table.
.. or changing the trigger function:

NEW.winPct := NEW.wins::numeric / (NEW.wins + NEW.losses);

Casting one of the numbers in the calculation to numeric (::numeric) forces the result to be numeric and preserves fractional digits.

I strongly suggest the second variant, since integer is obviously the right type for wins and losses. If your percentage doesn't have to be super-exact, I would also consider using a plain floating point type (real or double precision) for the percentage. Your trigger could then use:

NEW.winPct := NEW.wins::float8 / (NEW.wins + NEW.losses);

2.The unquoted column name winPct

It's cast to lower case and effectively just winpct. Be sure to read about identifiers in PostgreSQL.

3. Schema

Your table obviously lives in a non-standard schema: lab7.standings. Unless that is included in your search_path, the trigger creation has to use a schema-qualified name:

...
BEFORE INSERT OR UPDATE ON lab7.standings
...

P.S.

Goes to show the importance of posting your table definition with this kind of question.

Upvotes: 2

Hunter
Hunter

Reputation: 890

Try this:

CREATE TRIGGER update_winning_percentage
BEFORE INSERT OR UPDATE ON standings
FOR EACH ROW EXECUTE PROCEDURE calc_winning_percentage();

Upvotes: 2

Related Questions