How to insert trigger in PostgreSQL?

Question

I have two tables:

1. users
2. trainer

In the users table, I have a column is_trainer=false by default`.

When a user inputs the information in the table users and chooses the column is_trainer=true I want to insert the user information in the trainer table.

When I omit the if condition, after I insert a user in the users table it puts the information in the trainer table, but when I put if condition, it cannot work! I think it thinks that is_trainer is for the trainer table not for the users table

My code:

BEGIN
    IF NEW.is_trainer <> OLD.is_trainer THEN
        INSERT INTO trainer(trainer_id, sport_id, fee_per_hour, experience, created_by)
        VALUES(users.user_id, 3, '200$', 3, 6);
    END IF;
    RETURN NEW;
END;

Answer 1

This will not work as you expect:

IF NEW.is_trainer <> OLD.is_trainer THEN

In an INSERT trigger, there is not OLD value, ie all columns in pseudo table OLD are NULL. So your expression boils down to:

IF NEW.is_trainter <> NULL THEN

This condition can never be true, since nothing is different that NULL, nor equal to NULL (to check for nullness, you need IS NULL).

Based on your description of your requirement, I think that you want:

IF NEW.is_traiter = true THEN

Also, your INSERT command will not work, since it is referencing unknown relation users. You probably want:

INSERT INTO trainer(trainer_id,sport_id,fee_per_hour,experience,created_by)
VALUES(NEW.user_id, 3, '200$', 3, 6);

Likely, other values that you are giving for insert into trainer should also come from pseudo-table NEW (such as created_by for example).