0

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...

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.