Trigger on Update to update all the child nodes

After reading several articles of a hierarchical data tables, I've made my way into a table that looks like this : LOCAL:

|id|name        |parent_id|abbreviature | path        |
------------------------------------------------------- 
|1 |"Shoping"   |NULL     | "Sh"        | "Sh"        |
|2 |"Building A"|1        | "A"         | "Sh.A"      |
|3 |"Building B"|1        | "B"         | "Sh.B"      |
|4 |"Building C"|1        | "C"         | "Sh.C"      |
|5 |"Floor -1"  |2        | "-1"        | "Sh.A.-1"   |
|6 |"Floor 0"   |2        | "0"         | "Sh.A.0"    |
|7 |"Floor 1"   |2        | "1"         | "Sh.A.1"    |
|8 |"Floor 2"   |2        | "2"         | "Sh.A.2"    |
|9 |"Room 101"  |7        | "101"       | "Sh.A.1.101"|
|10|"Hospital"  |NULL     | "Hosp"      | "Hosp"      | 
|11|"Secretary" |10       | "Secrt"     | "Secrt"     |

and so on. This way it's particularly easy to select all the descendants of a node.

I've created this function to generate the path:

CREATE OR REPLACE FUNCTION teste1_trig () RETURNS TRIGGER AS '
  DECLARE
        dot    varchar := ''.'';
        npath local.path%TYPE;
  BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path:=NEW.abbreviature;
        RETURN NEW;
    ELSEIF NEW.parent_id IS NOT NULL THEN
        SELECT path INTO npath FROM local WHERE id=NEW.parent_id;
        NEW.path:=npath||dot||NEW.abbreviature;
        RETURN NEW;
    END IF;
  END;' LANGUAGE 'plpgsql';

And this function is activated by the following trigger :

CREATE TRIGGER trigger_teste
  BEFORE INSERT OR UPDATE
  ON local
  FOR EACH ROW
  EXECUTE PROCEDURE teste1_trig();

Every thing works flawless except when I update a abbreviature from a node that has childs, the childs remain with the old path.

I want to change the function to update all the childs from the current row. Here is the query to select all the childs :

SELECT id,name,parent_id FROM local WHERE id IN ( 
(WITH RECURSIVE parent AS
(
    SELECT id, parent_id  from local WHERE id = id_from_the_modified_node
    UNION ALL 
    SELECT t.id, t.parent_id FROM parent
    INNER JOIN local t ON parent.id =  t.parent_id
)

SELECT id FROM  parent
WHERE id <> id_from_the_modified_node) );

How can i change the function to be performed in a serie of rows if the abbreviature was updated (ie. change the name of abbreviature or change the parent_id)?

I'm using Postgresql v9.1.

Thank you in advance.

Answers


I've found the answer that i think it's the best for this case. Correct me if I'm wrong!

DECLARE 
id_upd INT[];
...
BEGIN
...

SELECT array ( SELECT id FROM local WHERE parent_id=NEW.id ) into id_upd; --- select direct childs
    IF id_upd IS NOT NULL THEN              
        FOREACH i IN ARRAY id_upd LOOP      
           UPDATE local SET path=NEW.path||'.'||abbreviature where id=i; --- update them
        END loop;       
    END IF;

END;

This way I just update all the direct childs of the node and the trigger fires again for the childs of the childs and so on.

Just don't forget to check if a parent's node is not a descendant of the node, this way you'll create a infinite loop.

best regards


Need Your Help

Bluetooth -> service discovery failed

android service bluetooth arduino discovery

I'm writing an application that is able to communicate with my PC. I have used the Bluetooth functionalities of the SDK 2.1.

How to output a Bootstrap button with glyphicon in CakePHP 3?

twitter-bootstrap cakephp cakephp-3.0 glyphicons

I need to output a button with a glyphicon like this with CakePHP 3.