更新表逻辑 [英] Update tables logic

问题描述

我有两个带有触发器的表。

I have two tables with triggers on them.

第一个

CREATE OR REPLACE FUNCTION update_table()
RETURNS trigger AS
$BODY$
 BEGIN
IF    TG_OP = 'UPDATE' THEN
    UPDATE filedata SET id=NEW.id,myData=NEW.myData,the_geom=ST_TRANSFORM(NEW.the_geom,70066) WHERE num=NEW.num;
    RETURN NEW;
ELSEIF TG_OP = 'INSERT' THEN
    INSERT INTO filedata(num,id,myData,the_geom) VALUES (NEW.num,NEW.id,NEW.myData,ST_TRANSFORM(NEW.the_geom,70066));
    INSERT INTO filestatus(id,name,status) VALUES (NEW.num,NEW.myData,'Не подтвержден');
    RETURN NEW;
END IF;

 END;
$BODY$
LANGUAGE plpgsql VOLATILE

SECOND

CREATE OR REPLACE FUNCTION update_table_temp()
RETURNS trigger AS
$BODY$
 BEGIN
IF    TG_OP = 'INSERT' THEN
    INSERT INTO filedata_temp(num,id,myData,the_geom) VALUES (NEW.num,NEW.id,NEW.myData,ST_TRANSFORM(NEW.the_geom,900913));
    RETURN NEW;
ELSEIF TG_OP = 'DELETE' THEN
    DELETE FROM filedata_temp WHERE num=OLD.num;
    RETURN OLD;
END IF;

 END;
$BODY$
LANGUAGE plpgsql VOLATILE

我有问题。如果我在第一个表中插入数据，其触发器也会在第二个表中插入数据。但是插入会导致第二个表的触发器在第一个表上进行插入，依此类推。

And I have a problem. If I insert data in the first table its trigger inserts data in the second table too. But that insert causes the second table's trigger to do an insert on the first table, and so on.

您能帮我这个忙吗？

UPDATE

i还有另一个问题
当我在表中插入数据时如何更改数据？例如，我在 the_geom 列中插入GEOMETRY。如果几何的 SRID = 70066 我想在 the_geom 列中使用此函数的结果 ST_TRANSFORM（the_geom，900913）。

UPDATE
i have another problem How to change data when i INSERT it in table? For example i insert GEOMETRY in the_geom column. And if geometry's SRID=70066 i want to put in the_geom column result of working of this function ST_TRANSFORM(the_geom,900913).

更新2

触发

  CREATE TRIGGER update_geom
  AFTER INSERT
  ON filedata_temp
  FOR EACH ROW
  EXECUTE PROCEDURE update_geom();

功能

 CREATE OR REPLACE FUNCTION update_geom()
 RETURNS trigger AS
 $$
 BEGIN
  IF ST_SRID(NEW.the_geom)=70066 THEN

    UPDATE filedata_temp SET id='88',the_geom=ST_TRANSFORM(NEW.the_geom,900913);
  END IF;
RETURN NEW;

END;
$$
LANGUAGE plpgsql;

如果我使用此功能，则不会触发任何工作，但如果这样：

If i use this function trigger no work but if this:

 CREATE OR REPLACE FUNCTION update_geom()
 RETURNS trigger AS
 $$
 BEGIN

    UPDATE filedata_temp SET id='88',the_geom=ST_TRANSFORM(NEW.the_geom,900913);
RETURN NEW;

END;
$$
LANGUAGE plpgsql;

我得到 id = 88 但ST_TRANSFORM没有工作。

i get id=88 but ST_TRANSFORM not work.

更新3

ST_TRANSFORM（）函数不错，但是它在我的情况。

ST_TRANSFORM() nice function but its do something strange in my case.

例如，我有一个表filedata_temp（SRID = 4326）。我使用srid = 70066插入几何图形，我尝试使用此触发器

For example i have a table filedata_temp(SRID=4326). I Insert geometry with srid=70066 i try this trigger

 CREATE OR REPLACE FUNCTION update_geom()
 RETURNS trigger AS
 $$
 BEGIN

    UPDATE filedata_temp the_geom=ST_TRANSFORM(NEW.the_geom,4326);
RETURN NEW;

END;
$$
LANGUAGE plpgsql;

并获得此几何。

ST_transform（）将此字符串从SRID = 4326和几何中转换为EPSG ：70066。

ST_transform() make this string from SRID=4326 and geometry which transform in EPSG:70066.

70066中有此字符串

There is this string in 70066

在4326

0103000020E61000000100000018000000AE4F5BA2FC5B4E407E80E7E6F46C4C40F7F1BF79255C4E4019C32D62086D4C40F7F1BF79255C4E4019C32D62086D4C4 0F7F1BF79255C4E4019C32D62086D4C40A7CE9382325C4E40D8EA369C0D6D4C40A7CE9382325C4E40D8EA369C0D6D4C401BD2B101575C4E4064A420982A6D4C4090DF29FE665D4E4064EE5369116D4C408195B3905C5D4E403664043C0B6D4C4025A00D0E4C5D4E40F7FD7274FD6C4C404201C7B5475D4E409ADF7B26F06C4C403801C7B5475D4E40E43D0EBFE46C4C406EC339053F5D4E404085D2B7DB6C4C40BDFDA836235D4E4001EBC841BE6C4C40685B445FFA5C4E4015C4038EB86C4C40ADB5C108AD5C4E40727935C6AA6C4C408A6B4B9BA25C4E40331ECEACAC6C4C40A7368928775C4E40F7C22E47B46C4C409F640F9D595C4E4077694F81B96C4C40660C21333B5C4E4012EA7C62C56C4C406623646D2C5C4E40CE83E38FCB6C4C4042D9EDFF215C4E40C6A89957D96C4C4095D75EC00F5C4E4013FFA0A5E66C4C40AE4F5BA2FC5B4E407E80E7E6F46C4C40

推荐答案

您有相互递归触发器和要防止递归。您而是希望触发器仅在用户的直接操作上触发，而不是通过触发器的操作触发。

You have mutually recursive triggers and you want to prevent the recursion. Your instead want a trigger to fire only on a direct action from a user, not an action via a trigger.

不幸的是，PostgreSQL不直接支持您想要的东西，您需要调整设计以避免相互递归。

Unfortunately, PostgreSQL doesn't directly support what you want, you'll need to tweak your design to avoid the mutual recursion.

更新的问题：在触发器中，更改 NEW 的内容，例如

Updated question: In a trigger, alter the contents of NEW, eg

IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
    NEW.the_geom := ST_TRANSFORM(NEW.the_geom,900913)
END IF;

请参见对于触发器确实非常好。

这篇关于更新表逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！