<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Dear Hubert,<br>
Two things<br>
1) <u><b>"statement-level" and "row-level" straight from PostgreSQL:
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html">http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html</a></b></u><br>
<ul>
<li>
<p><u><b>Statement-level triggers</b></u> 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. </p>
</li>
<li>
<p>The data change (insertion, update, or deletion) causing the
trigger to fire is naturally <span class="emphasis"><i class="EMPHASIS">not</i></span>
visible to SQL commands executed in a row-level before trigger, because
it hasn't happened yet. </p>
</li>
<li>
<p>However, SQL commands executed in a row-level before trigger <span
class="emphasis"><i class="EMPHASIS">will</i></span> 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. </p>
</li>
<li>
<p>When a <u><b>row-level</b></u> after trigger is fired, all data
changes made by the outer command are already complete, and are visible
to the invoked trigger function. </p>
</li>
</ul>
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 <br>
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.<br>
<br>
Thanks though.<br>
<br>
hubert depesz lubaczewski wrote:
<blockquote
cite="mid9e4684ce0703162139r7bc64180ga47e854af9678967(at)mail(dot)gmail(dot)com"
type="cite">On 3/16/07, louis gonzales <a class="moz-txt-link-rfc2396E" href="mailto:gonzales(at)linuxlouis(dot)net"><gonzales(at)linuxlouis(dot)net></a>
wrote:
<br>
<blockquote type="cite">I want to write a <big><big><big><b>statement-level</b></big></big></big>
trigger - one that happens once per
<br>
statement - such that, immediately after an insert into a table(which
<br>
gets a unique integer value as an ID from a defined sequence, being the
<br>
primary key on the table), a new table is created with foreign key
<br>
constraint on that unique ID.
<br>
</blockquote>
<br>
hi,
<br>
i think what you;re trying to do is wrong - having that many tables
<br>
simply cannot work properly.
<br>
additionally - i think you're misinformed. the kind of action you
<br>
would like to "trigger on" is not "per statement" but "per row".
<br>
example:
<br>
insert into table x (field) select other_field from other_table;
<br>
if this insert would insert 10 records - "once per statement" trigger
<br>
would be called only once.
<br>
<br>
but anyway - what you're proposing will lead to many, many problems.
<br>
(plus it will never scale correctly).
<br>
<br>
depesz
<br>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">--
Email: <a class="moz-txt-link-abbreviated" href="mailto:louis(dot)gonzales(at)linuxlouis(dot)net">louis(dot)gonzales(at)linuxlouis(dot)net</a>
WebSite: <a class="moz-txt-link-freetext" href="http://www.linuxlouis.net">http://www.linuxlouis.net</a>
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka
</pre>
</body>
</html>