In my PostgreSQL I have a parent table and a number of child tables (inherited from the parent table).

How to create a trigger which will be bind to all the current and future child tables derived from the parent table?

In a nutshell, what should be at "xxx" in the following code to refer to all the child tables?

create trigger trigger1
after insert or update of a, b, c, d on xxx
for each row
execute procedure trigger_procedure1();

closed as off-topic by PolyGeo, Paul, Mapperz Jul 17 '14 at 13:45

  • This question does not appear to be about geographic information systems (GIS) within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    I've asked a moderator to move this to dba.stackexchange.com. – Craig Ringer Jul 17 '14 at 9:17
  • Thx. Sorry, I didn't know about dba.stackexchange.com. – Zia Jul 17 '14 at 9:20
  • 2
    This question appears to be off-topic because it is about database administration and fits better on dba.stackexchange.com – PolyGeo Jul 17 '14 at 10:35

You can't.

Table inheritance is a bit limited. In particular, you can't have a foreign key that references all parts of an inherited table set, and you can't define triggers or constraints that're inherited by a set of tables.

  • hmm. Sad :( But when I am, lets say, inserting a row in my child table, the parent table is also getting updated (Actually they both are sharing the same data, I think there is no redundancy). Can I use this internal trigger (which is updating parent table) to activate my main trigger? – Zia Jul 17 '14 at 9:27
  • No you can't, because it's not an internal trigger, and it's not updating the parent table. Rather, it's doing the internal equivalent of a union all of all the tables when you SELECT from them. The data row only exists in one physical table. If you SELECT * FROM ONLY the_parent_table you'll see no rows from child tables for that reason. – Craig Ringer Jul 17 '14 at 9:28
  • ok. So can you suggest me another approach? I have n-number of clients. They own their respective tables. I want a trigger which will update a summary table (or lets say audit table), when there is an insertion in any one of those individual tables (These n-number of tables inherit one parent table). Thx a lot. – Zia Jul 17 '14 at 9:35
  • Create the trigger on each child table. They can share the same trigger function. – Craig Ringer Jul 17 '14 at 11:44
  • hmm.... Thx a lot. I was thinking the same. – Zia Jul 17 '14 at 11:58

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