Обсуждение: triggers and inheritance tree
Hi, i was trying to create triggers that redirect INSERT/UPDATE/DELETE actions from parent to childs, but found that UPDATE/DELETE doesn't get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE aren't even fired. I haven't tried with AFTER triggers to see if they are fired but i tried on 8.4 to 9.1 and all of these have the same behaviour attached is a simple contained test of this PS: i'm hoping this is just me needed to sleep -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Вложения
On Wed, Mar 28, 2012 at 1:21 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > Hi, > > i was trying to create triggers that redirect INSERT/UPDATE/DELETE > actions from parent to childs, but found that UPDATE/DELETE doesn't > get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE > aren't even fired. > and of course, it has nothing to do with the inheritance tree. that was just a coincidence. the problem occurs the same with normal tables, but i can't find where is the problem. i suspect, though, that is in the comparison in TRIGGER_TYPE_MATCHES -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Wed, Mar 28, 2012 at 9:16 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Wed, Mar 28, 2012 at 1:21 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: >> Hi, >> >> i was trying to create triggers that redirect INSERT/UPDATE/DELETE >> actions from parent to childs, but found that UPDATE/DELETE doesn't >> get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE >> aren't even fired. >> > > and of course, it has nothing to do with the inheritance tree. that > was just a coincidence. > > the problem occurs the same with normal tables, but i can't find where > is the problem. > i suspect, though, that is in the comparison in TRIGGER_TYPE_MATCHES I think the problem is that the UPDATE or DELETE can only fire once a matching row has been identified, so that OLD can be filled in appropriately. But in this case, the matching row gets found not in the parent table, but in one of its child tables. So any triggers on the child table would fire, but triggers on the parent table will not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Mar 28, 2012 at 8:29 AM, Robert Haas <robertmhaas@gmail.com> wrote: > > I think the problem is that the UPDATE or DELETE can only fire once a > matching row has been identified, so that OLD can be filled in > appropriately. But in this case, the matching row gets found not in > the parent table, but in one of its child tables. So any triggers on > the child table would fire, but triggers on the parent table will not. > ah! and of course that makes a lot of sense... how embarrasing! :( -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Wed, Mar 28, 2012 at 10:46 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Wed, Mar 28, 2012 at 8:29 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> I think the problem is that the UPDATE or DELETE can only fire once a >> matching row has been identified, so that OLD can be filled in >> appropriately. But in this case, the matching row gets found not in >> the parent table, but in one of its child tables. So any triggers on >> the child table would fire, but triggers on the parent table will not. > > ah! and of course that makes a lot of sense... > how embarrasing! :( If it's any consolation, when I initially looked at your example, I couldn't see what was wrong with it, either. After I ran it I figured it out. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company