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

No. Suspend only "pushes" out the record. EXIT; will actually stop the
code.

>>I need both.
>>
>>If I need to work with WHOLE table, will this work?

You are not going to be working with the whole table. Every time the After
Insert runs on either Table_A or Table_C it will only be referencing the
record it just inserted. So if you insert 1000 records into TABLE_A and 500
records into TABLE_C then the trigger code will be run for every record
inserted one a one-by-one basis. So the triggers will be run 1500 times
total.

>> (Reading this the impression I get is that only one record will be

inserted)

You are correct. Every insert runs one trigger which inserts one record.
If you do 2000 inserts on TABLE_A, you run 2000 After Insert triggers for
TABLE_A which insert 2000 records into TABLE_C. This should accomplish what
you want, which I beleive is to have TABLE_B contains parts of TABLE_A and
TABLE_C as records are created in both tables.

>> A Question: Is common a SP crash the Server?

I don't really know. Not unless the SP takes an enourmous amount of
resources and never returns (infinite loop). This is also what a
transaction timeout may help with. I have never had a SP crash the server.
I have had an SP crash the app though. The app was left waiting for the SP
to finish and it never could.

>>I need both.

Then have a trigger on both TABLE_C and TABLE_A. Every record inserted into
TABLE_C or TABLE_A causes a record to be inserted into TABLE_B with
TABLE_A's ID value and TABLE_C's FIELD VALUE.

I dont understand why you have TABLE_A and TABLE_C though. If TABLE_C is
linked to TABLE_A by ID, then are their just differences in the FIELD values
for TABLE_C and TABLE_A?

Also, are there multiple records with the same ID in TABLE_A or TABLE_C?

I just don't understand the relationships between the tables fully. You can
still do it, it just does not make any sense to me.

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

I dont think this will work. You don't want to work with the whole table
each time. That will cause an enourmous usage of resources. For every
insert on TABLE_A you are asking it to loop through all the records
previously inserted. So to insert 10 records, with 1000 already being
inserted, you are asking it to loop 10010 records. Not a good idea. It
should not be necessary since your objective seems only to have TABLE_B
contain the same number of records as TABLE_A and TABLE_C and TABLE_A's ID
and TABLE_C's FIELD.




-----Original Message-----
From: Carol Milius [mailto:lista@...]
Sent: Monday, February 14, 2005 3:56 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] newbie on SPs: How to imlement a wlhile
not eof loop

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



Yahoo! Groups Links