Обсуждение: Trigger/Rules Order of operations
I am interested in finding out the pros, cons, pitfalls of using the following design: Manual insert into Table A. Table A has a BEFORE INSERT trigger that causes an insert to table B. Table B has an AFTER INSERT trigger that causes an insert back to table A (With different criteria not an endless loop) Table A will have its Before Trig fire again and this time the criteria causes it to finish with a return new. Will the second insert into table A commit before the first insert into table A? What order does the insert into table B finish up? Ketema J. Harris www.ketema.net ketema@ketema.net ketemaj on iChat
I can't answer your question but I think you may have a serious database design issue at hand. Why not try to accomplish your goals in a simpler way? Regards, Ivan Pavlov On Dec 15, 12:49 pm, ket...@ketema.net (Ketema Harris) wrote: > I am interested in finding out the pros, cons, pitfalls of using the > following design: > > Manual insert into Table A. > Table A has a BEFORE INSERT trigger that causes an insert to table B. > Table B has an AFTER INSERT trigger that causes an insert back to > table A (With different criteria not an endless loop) > > Table A will have its Before Trig fire again and this time the > criteria causes it to finish with a return new. > > Will the second insert into table A commit before the first insert > into table A? What order does the insert into table B finish up? > > Ketema J. Harriswww.ketema.net > ket...@ketema.net > ketemaj on iChat > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
On Dec 16, 10:31 am, Ivan Pavlov <ivan.pav...@gmail.com> wrote: > I can't answer your question but I think you may have a serious > database design issue at hand. > Why not try to accomplish your goals in a simpler way? > > Regards, > Ivan Pavlov > > On Dec 15, 12:49 pm, ket...@ketema.net (Ketema Harris) wrote: > > > I am interested in finding out the pros, cons, pitfalls of using the > > following design: > > > Manual insert into Table A. > > Table A has a BEFORE INSERT trigger that causes an insert to table B. > > Table B has an AFTER INSERT trigger that causes an insert back to > > table A (With different criteria not an endless loop) > > > Table A will have its Before Trig fire again and this time the > > criteria causes it to finish with a return new. > > > Will the second insert into table A commit before the first insert > > into table A? What order does the insert into table B finish up? > > > Ketema J. Harriswww.ketema.net > > ket...@ketema.net > > ketemaj on iChat > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I am all for simple, but some times there is not a simple answer. Complex business rules don't always have a simple solution. And complex design is not necessarily bad design. I am tasked with creating a transaction system that has a lot of things occur automatically after certain input. In analyzing the task I saw two main paths. Use the trigger and rule system that Pg provides or construct external methods to control the logic and issue "simple" commands to insert data when appropriate. I chose to use the trigger and rule system because the DB is built for transactional applications. Isn't that the point of ACID and atomicity and all those other buzzwords? I did not want to have to recreate what the database already can do. I just want to make sure that my understanding of what I think is going to happen is on target, and I'm looking for experience from others, as well as the tests I am performing. So far it is working as expected, I'd appreciate any feedback from anyone who has done something similar to avoid stepping in the same potholes others may have discovered. Thanks
On 16/12/2008 16:26, Ketema wrote: > automatically after certain input. In analyzing the task I saw two > main paths. Use the trigger and rule system that Pg provides or > construct external methods to control the logic and issue "simple" > commands to insert data when appropriate. I chose to use the trigger You could encapsulate your business rules in functions (pl/pgsql or whatever) within the database....easier to understand what's going on (speaking as one who is completely ignorant about the rule system) and you get full ACIDity. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
2008/12/15 Ketema Harris <ketema@ketema.net>: > I am interested in finding out the pros, cons, pitfalls of using the > following design: > > Manual insert into Table A. > Table A has a BEFORE INSERT trigger that causes an insert to table B. > Table B has an AFTER INSERT trigger that causes an insert back to table A > (With different criteria not an endless loop) > > Table A will have its Before Trig fire again and this time the criteria > causes it to finish with a return new. > how do you will control that for each insertion on A don't insert on B when this tuple is just inserted? WHy i do this question? Becuase you can't put 2 before insert triggers at the same time (you can but is not cleany) I mean, if you want to do a 'control' of the inserted data i think is better a good trigger that filter data to insert, and if you want a log table make a rule or in the same function of the trigger put the logging > Will the second insert into table A commit before the first insert into > table A? What order does the insert into table B finish up? > the 2 triggers will execute, i think, at the same time. Just think: waht are the beneffits of this implementation? what do you want to resolve with it? > > Ketema J. Harris > www.ketema.net > ketema@ketema.net > ketemaj on iChat > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member