Subject Re: [firebird-support] Re: Need help with running for select... do loop through isql
Author Helen Borrie
At 05:13 PM 30/09/2005 +0000, you wrote:
>Basically i need to loop through a set of document_page records using
>the id and document_id of each record to insert a corresponding record
>into endorsement using the document_page's id and document_id (id and
>document_id are the concatinated key).

You are on the right track regarding the logic of what you have to do. You
are on the wrong track thinking that a script can achieve it. A script is
merely a list of DSQL statements that are executed one after the other as a
batch, from a client, like a robot sitting at the keyboard.

>I would like to have seen constraints manage this, but we didn't and I
>need a clean up script.

Constraints don't add data. What you want is triggers.

>Basically i need :
>for select id, document_id from docpage
>where (...)
>into :id, :document_id
>do begin
>insert into endorsement (id, document_id,...)
>values (:id,:document_id,...)

You need to write a one-off stored procedure to do this.

Then, as part of your corrective surgery, you need to add an After Insert
trigger to docpage, so that, in future, every insert to docpage will
automatically create these dependent records in endorsement:

If you are doing this from isql (or from a script), you'll need to set an
external terminator:

set term ^;

create trigger ai_docpage for docpage
active after insert
insert into endorsement (id, document_id)
values (, new.document_id);
end ^

This will reset the terminator:

set term ;^