Postgresql trigger function syntax error

Question

Hi this is my first trigger function on pgAdmin3 postgresql, I tried and have given up, I want to add a trigger to populate the item_sold table if an item has been sold. Im doing an assessment for etrading site.

CREATE TRIGGER trPopulateItemSold()
AFTER UPDATE ON tradebay.item FOR EACH ROW    
RETURN trigger AS $populate$
BEGIN
INSERT INTO item_sold (transaction_id, quantity, buyer_id, item_id) VALUES (transaction_id, NEW.quantity, buyer_id, NEW.item_id);
END;
LANGUAGE plpgsql;

SQL error:

ERROR:  syntax error at or near "("
LINE 1: CREATE TRIGGER trPopulateItemSold()
                                     ^

Craig Ringer · Accepted Answer · 2014-10-01 07:33:03Z

3

Your CREATE TRIGGER syntax is invalid. You're misunderstanding how it works.

Trigger creation is done in two steps:

Create the function that implements the trigger logic; then
Create a trigger that runs the function when something happens

See the PostgreSQL manual for details and examples:

You seem to be trying to mash the two together into a single step. That will not work. You need:

CREATE OR REPLACE FUNCTION .... RETURNS trigger AS $$
$$;

CREATE TRIGGER ...;

answered Oct 1, 2014 at 7:33

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql trigger function syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related