Pass information to another table with the same inherit

Question

Suppose the following scenario ... I have two tables (A, B) that inherit from a Z table, the SGBD is postgresql, these tables do not have an additional column, my question is how to pass a row from table A to table B that have the same inheritance.

The Table Z has a state column, with that column i decide which is the destiny of the data that has been inserted, this with a trigge, when execute a insert query the triger is activate and with condition is redirecte to child tables.

Now, if the state change, how can i pass to table A to table B.??

supose state is a varchar data type and the value is 'A' go to table A and if the value is 'B' go to table B, the constranint in the tables is something like that... table A constraint.. CONSTRAINT only_A CHECK (state = 'A') table B constraint.. CONSTRAINT only_A CHECK (state = 'B')

____________
|___Z_TABLE_|
| pk        |   
| col1      | 
| col2      | 
| col3      | 
| state     |
|___________| 

_______________    ________________
|___A_TABLE____|   |____B_TABLE____|
| INHERITS (Z) |   | INHERITS (Z)  |
|______________|   |_______________|

i need to delete and reinsert in the other table or only with chage the value of state column ???

something more hard is... the table Z has a many relation with another tables...

Answer 1

You can create trigger after update like this

CREATE TRIGGER trigger_name
    AFTER UPDATE ON Z
    FOR EACH ROW
    ... delete/update/insert to table A;
    ... delete/update/insert to table B;

I hope this helps

Reference https://www.postgresql.org/docs/10/sql-createtrigger.html