Обсуждение: trigger fired on changes in specific column
I would like to fire a trigger when the value in a single column have changed. I read somewhere that this is in the SQL-standard but not in postgresql, I don't have the SQL-spec so I can't check, and I don't know what version of the standard. I can't find it in the todo list though, either it's missing and should be added or it's not there by intention. I have a table where the rows are partitioned into small groups. When a value in column A changes, then every value in the same group would have their column B value updated. This would in turn trigger more things, If the trigger is fired only when column A changes the problem is solved. After this I need to do almost the same procedure where a value of column B is changed to update the column C for all rules in the group. This ordering of triggers would work automatically if they fire just when the correct column are updated. Well, I can work around these things. One way is to place B and C in their own tables so updating these don't trigger anything in the original table. I also think I can keep them in the relation by being carefull and not update unless the value actually changed, this costs a litle extra CPU time. If i'm lucky it's even fixed in 7.3 and then I can live without it for a while. -- /Dennis
On Friday 23 Aug 2002 7:52 am, Dennis Bjorklund wrote: > I have a table where the rows are partitioned into small groups. When a > value in column A changes, then every value in the same group would have > their column B value updated. This would in turn trigger more things, If > the trigger is fired only when column A changes the problem is solved. > > After this I need to do almost the same procedure where a value of column > B is changed to update the column C for all rules in the group. This > ordering of triggers would work automatically if they fire just when the > correct column are updated. > > Well, I can work around these things. One way is to place B and C in their > own tables so updating these don't trigger anything in the original table. > I also think I can keep them in the relation by being carefull and not > update unless the value actually changed, this costs a litle extra CPU > time. A single IF OLD.x=NEW.x for each column won't slow you down noticably. Indeed, if changes cascade A=>B=>C then you might well be able to optimise for case A. However, I think you need to look at your data definitions - this sound horribly unnormalized. I'm not a normalisation fascist, but this just feels *very* wrong. Is there a good reason why you don't decompose B,C etc to their own tables and join where necessary. You can probably keep things looking the same (or at least very similar) using an appropriate view. Of course, you might have damn good reasons why none of the above helps in this case. HTH - Richard Huxton
On Fri, 23 Aug 2002, Richard Huxton wrote: > However, I think you need to look at your data definitions - this sound > horribly unnormalized. I'm not a normalisation fascist, but this just feels > *very* wrong. Well, the values are redundant and calculated from other values in the table. It used to be in a view calculated from the rest. But the calculation takes a lot of time so I have to store it somewhere. The problem is that the calculation formula gives that when I change one value in a group all the calculated values for the group have to be recalculated. Before I added these calculated values the database was in BCNF. Now it is not, but it will not really change anything if I keep these values in there own table or not. > Is there a good reason why you don't decompose B,C etc to their own tables and > join where necessary. You can probably keep things looking the same (or at > least very similar) using an appropriate view. I can do this (and probably will), and what it helps me is just so the triggers works (esier). It does not really change the problem of storing redundant data in the database. But the world is not perfect, I have to precalculate and store these values or it is to slow. I think your recomendation is both valid and good. If I could find a better way to handle this I would. Maybe something like a view that caches values or something, but I have not found that in postgresql. -- /Dennis
On Friday 23 Aug 2002 12:03 pm, Dennis Bjorklund wrote: > On Fri, 23 Aug 2002, Richard Huxton wrote: > > However, I think you need to look at your data definitions - this sound > > horribly unnormalized. I'm not a normalisation fascist, but this just > > feels *very* wrong. > > Well, the values are redundant and calculated from other values in the > table. It used to be in a view calculated from the rest. But the > calculation takes a lot of time so I have to store it somewhere. > > The problem is that the calculation formula gives that when I change one > value in a group all the calculated values for the group have to be > recalculated. If the whole group shares the same result value, having separate lookup tables is probably a win. > I think your recomendation is both valid and good. If I could find a > better way to handle this I would. Maybe something like a view that caches > values or something, but I have not found that in postgresql. You could make a temporary table (yuck) but you might want to look at the is_cacheable flag you can add to function definitions. That tells the planner that for any given input value(s) the output is fixed. Primarily useful on the WHERE side of things, but might be worth looking at. - Richard Huxton
Dennis Bjorklund <db@zigo.dhs.org> writes: > I would like to fire a trigger when the value in a single column > have changed. I read somewhere that this is in the SQL-standard but > not in postgresql, I don't have the SQL-spec so I can't check, and I > don't know what version of the standard. Yes, it's in the standard (SQL99). > I can't find it in the todo list though, either it's missing and > should be added or it's not there by intention. It should probably be there -- I can't see a good reason not to implement it, at any rate. > If i'm lucky it's even fixed in 7.3 and then I can live without it > for a while. It's not in CVS HEAD. Given that no one is currently working on it AFAIK, it almost certainly won't be in 7.3 Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC