Postgresql: Creating a Trigger

Question

I have to create a trigger in postgresql (without any function calls). I have two tables LivesIn(pid, name, province) and Places(name, province, population, mayorid)

I have the following code:

CREATE TRIGGER updatePopulation
AFTER INSERT ON LivesIn
FOR EACH ROW  
UPDATE Places
SET NEW.population = OLD.Population + 1
WHERE LivesIn.name = Places.name AND LiveIn.province = Places.province;

I am getting the following error: psql:/home/2008/uehtes/Desktop/Comp421/comp421_a2_q1.sql:111: ERROR: syntax error at or near "UPDATE" LINE 5: UPDATE Places.population ^

Any help would be really appreciated.

ueg1990 · Accepted Answer · 2013-03-06 04:29:33Z

2

@Glenn, apparently that is not the way to do it. You have to create a function and call it in the trigger. Like this:

CREATE FUNCTION updatePop() RETURNS TRIGGER AS $updatePopulation$
DECLARE
    name1 varchar(30);
    name2 varchar(30);
BEGIN
    IF (TG_OP = 'INSERT') THEN
        name1 = NEW.name;
        name2 = NEW.province;
        UPDATE Place
        SET population = population + 1
        WHERE name1 = Place.name AND name2 = Place.province;
END IF;
RETURN NULL;    
END;
$updatePopulation$ LANGUAGE plpgsql;

CREATE TRIGGER updatePopulation
AFTER INSERT ON LivesIn
FOR EACH ROW 
EXECUTE PROCEDURE updatePop();

answered Mar 6, 2013 at 4:29

ueg1990

1,0332 gold badges21 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: Creating a Trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related