CODEWITHSUNDEEP
sqlpostgresqltriggersaggregate-functions
Vasko
Vasko

Reputation: 257

Postgresql trigger function syntax error

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()
                                     ^

Upvotes: 2

Views: 1502

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

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 ...;

Upvotes: 3

Related Questions