Re: [GENERAL] Trigger/Rules Order of operations
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger/Rules Order of operations
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger/Rules Order of operations
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 databaseeasier 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 r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger/Rules Order of operations
2008/12/15 Ketema Harris ket...@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 ket...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 ket...@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
