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.

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

Forgive me if I have interpreted your pseudo code wrong. As I
understand, you want to insert each tc value into tb alongside the
current ta id?

Your before insert trigger code could look like:

begin
for select distinct campo
from tc
into :campo
do
begin
insert into tb (id, campo) values (new.id, :campo);
end
end
^

You would need to make sure that tc wasn't too big though, as every
insert into ta would need to then read the entire tc, and then insert
that many records into tb

Adam






--- In firebird-support@yahoogroups.com, "Carol Milius" <lista@m...>
wrote:

> >> The SUSPEND is if your SP has an output variable.
> >> Every SUSPEND pushes one record out of the SP
> >> with the current value assigned to it.
>
> hmmm... it explains a lot of exemples i've seen.
> Now I am begining to understand 'how' a SP works..
>

>
> >>Now you are doing an "AfterPost" event which I take to mean After

Insert.

>
> Yes, you are right.
> :)
>
> >>The trigger you are using to do this only works with the current

record

> >>only. So your code would be working with the whole table every

time a

> >>record is inserted. I am not sure that is what you intended. I

don't think

> >>you need to loop through all the records every time.
>
> >>I think what you want is the After Insert trigger on Table A.

You then want

> >>to insert a record that contains the FIELD value from Table C,

and the ID

> >>value from Table A into Table B.
>
> I need both.
>
> If I need to work with WHOLE table, will this work?
>
> //Table_A.AfterInstert
>
> FOR SELECT A_FIELD FROM TABLE_C1 DO
> BEGIN
> INSERT INTO TABLE_B(ID,FIELD) VALUES(NEW.ID, A_FIELD);
> SUSPEND;
> END
>
> (Reading this the impression I get is that only one record will be

inserted)

>

>
>
> >> Hope this helps,
>
> Very much!
>
> Carol.
>
>
>
>
>
> I was much in the same boat, myself. First things first...
>
> >while not TC.EOF do
>
> There is no such thing for SP's. That kind of code is only in ADO,

DAO,

> etc.
>
> In order to loop through records you need to do an actual SELECT

statement.

> If you wanted all records from a table it would be SELECT * FROM

TABLE.

>
> To work with it in SP, you would use the following statment:
>
> FOR SELECT * FROM TABLE1 DO
> BEGIN
> <Code>
> SUSPEND;
> END
>
> This would select all records, but you are not limited to that.

You can put

> any kind of WHERE, ORDER BY, GROUP CLAUSE you want to use. The

SUSPEND is

> if your SP has an output variable. Every SUSPEND pushes one record

out of

> the SP with the current value assigned to it.
>
> Now you are doing an "AfterPost" event which I take to mean After

Insert.

> The trigger you are using to do this only works with the current

record

> only. So your code would be working with the whole table every

time a

> record is inserted. I am not sure that is what you intended. I

don't think

> you need to loop through all the records every time.
>
> I think what you want is the After Insert trigger on Table A. You

then want

> to insert a record that contains the FIELD value from Table C, and

the ID

> value from Table A into Table B. I think this is what you want

then.

>
> DECLARE VARIABLE TEMP_VAL VARCHAR(50);
>
> BEGIN
>
> SELECT FIELD FROM TABLE_C WHERE TABLE_C.ID = NEW.ID INTO :TEMP_VAL;
>
> INSERT INTO TABLE_B(ID,FIELD) VALUES(NEW.ID,:TEMP_VAL);
>
> END
>
>
> The "NEW" allows you access the current values for the record in

TABLE_A.

> NEW.ID and NEW.FIELD represent what was just inserted into TABLE_A.
>
>
>
> Hope this helps, and anybody else please comment on this.
>
> -----Original Message-----
> From: Carol Milius [mailto:lista@m...]
> Sent: Monday, February 14, 2005 10:13 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] newbie on SPs: How to imlement a wlhile

not

> eof loop
>
>
>
> Hi!
>
> I am reading some manual, inlcuding material on IBPhoenix but I am

a 'little

> slow' and the code I wrote is not working.. any tips?
>
> 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
>
> Something like:
>
> Begin
> // AfterPost on TA
> begin
> while not TC.EOF do
> begin
> TB.ID := TA.ID
> TB.Campo := TC.Campo
> TC.next;
> end;
> end;
> end;
>
>
> Best Regards,
>
>
> Carol.