PostgreSQL:创建触发器 [英] Postgresql: Creating a Trigger
问题描述
我必须在postgresql中创建一个触发器(没有任何函数调用)。我有两个表LivesIn(pid,名称,省)和Places(名称,省,人口,市长)
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)
我有以下代码:
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;
我遇到以下错误:
psql:/ home / 2008 / uehtes / Desktop /Comp421/comp421_a2_q1.sql:111:错误: UPDATE
或附近的语法错误第5行:UPDATE Places.population
^
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.
推荐答案
@Glenn,显然不是这样做的方法。您必须创建一个函数并在触发器中调用它。像这样:
@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();
这篇关于PostgreSQL:创建触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!