Thread: Rule or Function and Trigger?
I am beginning to migrate some functionality from an application prototype into the DBMS. One of these elements is time stamping row insertions and updates. I have been reading about rules, functions and triggers in regards to this issue. So, of course, now I have myself completely befuddled. From what I can determine from the documentation, it makes little difference to the DBMS whether I set a rule on insert and one on update for each table or define a function and set a trigger for insert or update on each table. If this is indeed the case then which way is the preferred method? If one looks at things from a code maintenance standpoint it appears the the function and trigger approach is preferable. Is it? Opinions? -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
"James B. Byrne" <byrnejb@harte-lyne.ca> writes: > I am beginning to migrate some functionality from an application > prototype into the DBMS. One of these elements is time stamping row > insertions and updates. > I have been reading about rules, functions and triggers in regards > to this issue. So, of course, now I have myself completely > befuddled. From what I can determine from the documentation, it > makes little difference to the DBMS whether I set a rule on insert > and one on update for each table or define a function and set a > trigger for insert or update on each table. > If this is indeed the case then which way is the preferred method? A trigger is very much preferred. Rules are not as easy as they look. Triggers are a bit more notationally cumbersome, but they tend to do what you expect without any surprises. For the problem you mention, a stripped-down version of the first example here is what you want: http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html regards, tom lane
Il giorno 03/apr/09, alle ore 16:58, Tom Lane ha scritto: > "James B. Byrne" <byrnejb@harte-lyne.ca> writes: >> I am beginning to migrate some functionality from an application >> prototype into the DBMS. One of these elements is time stamping row >> insertions and updates. > >> I have been reading about rules, functions and triggers in regards >> to this issue. So, of course, now I have myself completely >> befuddled. From what I can determine from the documentation, it >> makes little difference to the DBMS whether I set a rule on insert >> and one on update for each table or define a function and set a >> trigger for insert or update on each table. > >> If this is indeed the case then which way is the preferred method? > > A trigger is very much preferred. Rules are not as easy as they look. > Triggers are a bit more notationally cumbersome, but they tend to > do what you expect without any surprises. Are there some situations when using a rule is clearly superior than a trigger? I experimented with rules, with good result, but I’d rethink my approach if triggers are more "reliable". Thank you in advance -- Giorgio Valoti