Home>

I'm currently studying postgreSQL triggers ...! Has an error and the cause and response are unknown, so please let me know.

What i did

I made the following stored procedure. After creating new user data, y is automatically added to test_flg.

Stored procedure creation
CREATE OR REPLACE FUNCTION update_user () RETURNS TRIGGER AS $$
    BEGIN
        UPDATE User SET test_flg ='Y' WHERE test_flg ='N' and No like'No-M%';
    END;
$$LANGUAGE plpgsql;
Registered as a trigger function
CREATE TRIGGER check_update
    AFTER INSERT ON User
    FOR EACH ROW
    EXECUTE PROCEDURE update_user ();
The error that is occurring
DBExecuteError: ERROR: control reached end of trigger procedure without RETURN --Where: PL/pgSQL function

I don't need the return value this time, but I get a RETURN error.
What should I do here ...

  • Answer # 1

    CREATE OR REPLACE FUNCTION update_user () RETURNS TRIGGER AS $$

    Does FUNCTION require RETURN?

    So

    trigger procedure without RETURN --Where: PL/pgSQL function
    I'm angry.

    EXECUTE PROCEDURE update_user ();

    It should be FUNCTION, but it is called by PROCEDURE.

    If test_flg ='N' and No like'No-M%', why not just set the BEFORE INSERT trigger to code that just sets test_flg to'Y'?

  • Answer # 2

    For triggers in the first placeFunction(RETURNS TRIGGER is required), so be surereturnIs required.
    * Return is not necessary for Function (returns void) that has no return value regardless of the trigger.

    The return value is not particularly necessary this time

    return Null;


    Please add.