how to use trigger add column value while relate table insert record?

Question

I created the following PostgreSQL function and the corresponding trigger. I want to insert data into the user_star_raider table, the stars column data in the raider table can automatically increase by 1, but now I use the current function and trigger and it does not meet expectations The result, can you help me?

raider table:

 raider_id | stars | visits
-----------+-------+--------
 13243     |     0 |   4525

user_star:

 user_id | raider_id | star_date
---------+-----------+-----------

I create function and trigger:

CREATE OR REPLACE FUNCTION auto_increase_star() RETURNS TRIGGER AS $$
    BEGIN
        UPDATE raider SET stars = stars + 1 WHERE raider.raider_id = new.raider_id;
        RETURN new;
    end
$$
LANGUAGE plpgsql;

CREATE TRIGGER add_user_star_raider_trigger
    AFTER INSERT ON user_star_raider FOR EACH ROW EXECUTE PROCEDURE auto_increase_star();

When I insert a row ('[email protected]', '13243', '2020-07-25') to use_star_raider, I want the raider stars column to be increased by 1.

Is there any good solution?

Answer 1

As mentioned in the comment, your code works. Here is a db<>fiddle.

I realize that I made one change in the db<>fiddle to facilitate typing -- and that might be the cause of your problem. I changed the raider_id from a string to a number: fewer pesky quotes to deal with.

That, in turn, means that the values necessarily match; integers have the nice property that what you see is what you get. However, with strings, strange things can happen -- such as hidden characters or look-alikes from different character sets. What may be happening is that everything works, but the raider_id values don't match between the tables. My advice is to set up a foreign key relationship, to be sure that the raider_id in the second table is a valid raider_id based on the first table.