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

A question: The SUSPEND stops the routine? (Like an EXIT in code?)

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

A Question: Is common a SP crash the Server?

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