current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 2 down vote favorite

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()
                                     ^
share|improve this question
up vote 2 down vote accepted

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 ...;
share|improve this answer
    
Thank you Craig that makes more sense now, cheers – Vasko Oct 1 '14 at 7:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.