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
|
