At 12:22 AM 15/02/2005 +0000, you wrote:

>Hi Carol,
>
>After reading your initial post, I was not quite sure what you were
>trying to accomplish. You seemed pretty confident that you were after
>a trigger, but I for one am not sure whether that will best suit your
>purpse. Perhaps if you can provide an example with a couple of
>records and your desired behaviour, we can be a bit more assistance.
>
> > A question: The SUSPEND stops the routine? (Like an EXIT in code?)
>
>Nope, suspend does not exit the routine. It just adds another row to
>the returned records. I don't think you will need suspend though if
>you are only using a trigger.

Actually, in a trigger, the answer to this question is YES, suspend acts
exactly like EXIT. It causes execution of the trigger to terminate
immediately, with unpredictable results. Make it an absolute rule NEVER to
use SUSPEND for any purpose except to output a row to the buffer.

SUSPEND belongs in a selectable SP and in no other place - not executable
SPs and not triggers. What it does is literally suspend execution of the
procedure until the client requests the latest output row with a Fetch call.

> > A Question: Is common a SP crash the Server?
>
>No, not unless your SP calls a UDF function which crashes, BUT
>
>SPs can have undesirable consequences. For example, if you call a SP
>from a trigger, and then that SP inserts / updates / deletes
>something, another trigger (or even the same one) will fire, and if
>you are not careful, you may get into an infinite loop (although from
>memory FB gives up after a while).

Tattoo this on your forehead.

SPs and triggers are a whole programming environment. PSQL mightn't look
much like a programming language but that's what it is. Like any program
module, a SP or a trigger doesn't behave nicely if designed in isolation
from the things it depends on.

Earlier, you wrote:

> > Tables:
> > Table A (ID:integer; FIELD:String)
> > Table B (ID:integer; FIELD:String)
> > Table C (ID:integer; FIELD:String)
> >
> > ID's are set by Generator (Trigger onBeforeInsert).
> >
> > The Goal:
> > I would like to call a procedure on AfterPost event.
> > This procedure would insert records on table B (TB)
> > The records to be inserted on TB are from TA and TC

A WHILE loop is not wanted here. You want an AFTER INSERT trigger on the
table that is receiving the initial insert. PSQL has its own syntax for
looping through a set.

Do something like the following:

create trigger tba_ai for TA
active after insert position 0
as
declare variable temp_campo integer; /* or whatever is required */
begin
for select Campo from TC
where id = new.id do
into :temp_campo do
insert into TB (id, Campo)
values (new.id, :temp_campo);
end

Well, there's a bit of guesswork here about how the data of the three table
relate to one another, but you get the drift. Keep everything server side,
nicely atomic (all will succeed or all will fail).

And - REPEAT - do watch out what the insert triggers on table TB are going
to do in response to this code. Triggers are row-level, and fire on every
row that gets the operation.

./heLen