how to fix this trigger error in PostgreSQL [duplicate]

Question

I am getting a syntax error for my code which I can't understand why am I missing something? also, I read this I did not get my answer syntax Error in PostgreSQL when I try to create Trigger

CREATE TRIGGER MyExampleName AFTER INSERT ON baskets
FOR EACH ROW BEGIN
    UPDATE customers 
        SET customers.credit=customers.credit - NEW.amount 
        WHERE customers.id = NEW.customer_id;
END;

and tried it like this as well:


CREATE TRIGGER MyExampleName AFTER INSERT ON baskets
FOR EACH ROW AS $$ BEGIN
    UPDATE customers 
        SET customers.credit=customers.credit - NEW.amount 
        WHERE customers.id = NEW.customer_id;
END;
$$ LANGUAGE plpgsql;

Error:

ERROR:  syntax error at or near "BEGIN"
LINE 2: FOR EACH ROW BEGIN
                     ^
SQL state: 42601
Character: 67

Please read the linked question more carefully. A CREATE TRIGGER does not include the actual trigger code in the body. You need a separate CREATE FUNCTION A complete example is also available in the manual — user330315
– user330315, Commented Jan 31, 2022 at 20:03

oxfist · Accepted Answer · 2022-01-31 20:12:45Z

I'd say the first comment on your question pretty much covers it all. You cannot put the trigger code in the trigger body, you must first create a separate function and include the function call inside the trigger body.

This example comes directly from the Postgres docs:

-- 1. Create the function that does what you need
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

-- 2. Create the trigger with the 'EXECUTE FUNCTION function_name' part
--    replacing the actual function name from step 1.
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

Collectives™ on Stack Overflow

how to fix this trigger error in PostgreSQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related