louis gonzales wrote:
Dear Hubert,
Two things
1) _*"statement-level" and "row-level" straight from PostgreSQL:
http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html*_
*
_*Statement-level triggers*_ follow simple visibility rules:
none of the changes made by a statement are visible to
statement-level triggers that are invoked before the statement,
whereas all modifications are visible to statement-level after
triggers.
*
The data change (insertion, update, or deletion) causing the
trigger to fire is naturally /not/ visible to SQL commands
executed in a row-level before trigger, because it hasn't
happened yet.
*
However, SQL commands executed in a row-level before trigger
/will/ see the effects of data changes for rows previously
processed in the same outer command. This requires caution,
since the ordering of these change events is not in general
predictable; a SQL command that affects multiple rows may visit
the rows in any order.
*
When a _*row-level*_ after trigger is fired, all data changes
made by the outer command are already complete, and are visible
to the invoked trigger function.
2) Seeing as you have no idea - not attacking, stating fact - on the
rationale behind the "insert statement-level" to create 1-to-1 table
for each statement-level
insert, I'd say your presumption is unfounded. If you have some
benchmark data, which support why/how to quantify, 50K records in a
single table, all of which would have N number of associated records
in another table, would out perform 50K records in a single table
referencing dedicated 'small' tables, please do share.
Thanks though.
hubert depesz lubaczewski wrote:
On 3/16/07, louis gonzales <gonzales@xxxxxxxxxxxxxx> wrote:
I want to write a *statement-level* trigger - one that happens once per
statement - such that, immediately after an insert into a table(which
gets a unique integer value as an ID from a defined sequence, being the
primary key on the table), a new table is created with foreign key
constraint on that unique ID.
hi,
i think what you;re trying to do is wrong - having that many tables
simply cannot work properly.
additionally - i think you're misinformed. the kind of action you
would like to "trigger on" is not "per statement" but "per row".
example:
insert into table x (field) select other_field from other_table;
if this insert would insert 10 records - "once per statement" trigger
would be called only once.
but anyway - what you're proposing will lead to many, many problems.
(plus it will never scale correctly).
depesz
--
Email: louis.gonzales@xxxxxxxxxxxxxx
WebSite: http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka
--
Email: louis.gonzales@xxxxxxxxxxxxxx
WebSite: http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka
