Added a trigger function for inserts on a TABLE, but the function is not receiving the value from NE-CodePudding

I have created a Trigger function, and have added it to a table. I've also tested the function to make sure it's firing by replacing my NEW.user_id value in the trigger function with a static one (user_id = 1) and it's being fired and the query inside the function as happening as expected.

What am I doing incorrectly that using NEW. doesn't get the value from the newly inserted row?

My Trigger Function:

CREATE OR REPLACE FUNCTION test_trigger() 
RETURNS trigger language plpgsql 
AS $$
    BEGIN
    
    WITH cte AS(
        SELECT * FROM test_user WHERE user_id = NEW.user_id
    )
    UPDATE test_username
    SET money_counter = money_counter   1, 
        total_revenue = total_revenue   cte.revenue
    FROM cte
    WHERE test_username.username = cte.username;
    RETURN NULL;
    
    END; $$

Creating the Trigger:

CREATE TRIGGER test_trigger AFTER INSERT 
ON test_user EXECUTE FUNCTION test_trigger();

The Table I have added a Trigger function to:

Using an insert query to test the trigger function:

INSERT INTO test_user(
    user_id,
    username,
    revenue
)VALUES(
    1,
    'cooldude',
    1
)

Nothing happens to my test_username table which the Trigger function was supposed to update.

I try it again by changing the trigger function to a static value of 1 for the user_id and the trigger function updates it as expected:

INSERT INTO test_user(
    user_id,
    username,
    revenue
)VALUES(
    1,
    'cooldude',
    1
)

Why isn't my NEW.user_id in the dynamic trigger function receiving the value from the newly inserted row into my test_user table?

CodePudding user response：

The issue is here:

CREATE TRIGGER test_trigger AFTER INSERT 
ON test_user EXECUTE FUNCTION test_trigger();

No FOR EACH clause was specified so the trigger fell back to the default of FOR EACH STATEMENT.

Per the docs plpgsl trigger function in the FOR EACH STATEMENT case:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

To get access to the NEW record change the trigger definition to:

CREATE TRIGGER test_trigger AFTER INSERT 
ON test_user FOR EACH ROW EXECUTE FUNCTION test_trigger();