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

GOT IT!!

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

Already made!!


Helen, thanks a lot for your time!

Precious answers. Safely copied to me notebook.

Best Regards,


Carol.




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