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 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