SQL PostgreSQL 更新触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25271883/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Update trigger
提问by Kliver Max
I have a table:
我有一张桌子：
CREATE TABLE annotations
(
gid serial NOT NULL,
annotation character varying(250),
the_geom geometry,
"rotationAngle" character varying(3) DEFAULT 0,
CONSTRAINT annotations_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
And trigger:
并触发：
CREATE TRIGGER set_angle
AFTER INSERT OR UPDATE
ON annotations
FOR EACH ROW
EXECUTE PROCEDURE setangle();
And function:
和功能：
CREATE OR REPLACE FUNCTION setAngle() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE annotations SET "rotationAngle" = degrees( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE annotations SET "rotationAngle" = degrees( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
And when new row inserted in table or row edited i want to field rotationAnglesetted with function result.
But when i inserting a new row in table function not work. I mean thath rotationAnglevalue not changed.
当新行插入表格或编辑的行时，我想rotationAngle用函数结果设置字段。但是当我在表函数中插入新行时不起作用。我的意思是那个rotationAngle值没有改变。
What can be wrong?
有什么问题？
回答by Erwin Brandstetter
As @SpartanElite pointed out, you are triggering an endless loop.
正如@SpartanElite 指出的那样，您正在触发一个无限循环。
Simplify the trigger function:
简化触发函数：
CREATE OR REPLACE FUNCTION set_angle()
RETURNS TRIGGER AS
$func$
BEGIN
NEW."rotationAngle" := degrees(
ST_Azimuth(
ST_StartPoint(NEW.the_geom)
, ST_EndPoint(NEW.the_geom)
)
) - 90;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
- Assign to
NEWdirectly. NoWHEREin this case. - You mustdouble-quote illegal column names. Better not to use such names to begin with.
Recent related answer. - Code for insert & upgrade is the same. I folded into one code path.
NEW直接分配给。WHERE在这种情况下没有。- 您必须用双引号引用非法的列名。最好不要一开始就使用这样的名字。
最近的相关回答。 - 插入和升级的代码是相同的。我折叠成一个代码路径。
Use a BEFOREtrigger. This way you can edit columns of the triggering row directly beforethey are saved:
使用BEFORE触发器。通过这种方式，您可以在保存之前直接编辑触发行的列：
CREATE TRIGGER set_angle
BEFORE INSERT OR UPDATE ON annotations
FOR EACH ROW EXECUTE PROCEDURE set_angle();However
然而
Ifyou are just trying to persist a functionally dependent value in the table (and there are no other considerations): Don't. Use a view or a generated column instead:
如果您只是想在表中保留一个功能相关的值（并且没有其他考虑因素）：不要. 改用视图或生成的列：
Then you don't need any of this.
那你就不需要这些了。
回答by SpartanElite
There are multiple things wrong here.
1) When you insert a row 'A' the function setAngle()is called. But in the function you are calling another updatewithin the function which will trigger the function again, and again, and so on...To fix this don't issue a update! Just update the NEW records value independently and return it.
这里有很多错误。1) 当您插入一行 'A' 时，该函数setAngle()被调用。但是在函数中，您正在调用函数中的另一个update函数，这将再次触发该函数，依此类推……要解决此问题，请不要发布更新！只需独立更新 NEW 记录值并返回它。
