Re: Trigger/Rules Order of operations

On Dec 16, 10:31 am, Ivan Pavlov <ivan(dot)pav(dot)(dot)(dot)(at)gmail(dot)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(dot)(dot)(dot)(at)ketema(dot)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(dot)(dot)(dot)(at)ketema(dot)net
> > ketemaj on iChat
>
> > --
> > Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)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