Is your intent to insert a record into Table_B with the ID of the "most
recently" inserted record from TABLE_A and the FIELD data from TABLE_C where
TABLE_C.ID = TABLE_A.ID?

If this is what you are trying to accomplish by going through all the
records of TABLE_A to the end every time, then what you are really after is:

DECLARE TEMP_ID INTEGER;
DECLARE TEMP_FIELD VARCHAR(50); /* I am guessing about the length of the
string here */

SELECT MAX(ID) FROM TABLE_A INTO :TEMP_ID;
SELECT FIELD FROM TABLE_C WHERE TABLE_C.ID = TABLE_A.ID INTO :TEMP_FIELD;

INSERT INTO TABLE_B(ID,FIELD) VALUES(TEMP_ID,TEMP_FIELD);

The MAX() function will search through the entire table for the Maximum
value of ID. If you have a PK on this it will be fairly fast.

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



Edwin,

Thanks a lot for your time.
reading the posts now I can see how disturber were my concepts about Stored
Procedure.

You gave a good help on tis.

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

What I meant is what Adam wrote:

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

Thanks Again!

Carol

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



Yahoo! Groups Links