• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3983
  • Last Modified:

Firebird cursor loop in triggers

Hi,


Firebird 2.x

Can anyone tell me if its possible to create a cursor loop in a firebird trigger?  If not can it be done using a SP and calling from the trigger?

The scenario is that I have 3 tables, A, B and C.  A and B are master detail and C is a lookup for B.  After an insert of a record into table A I want to selected all records in table C and insert them into table B, key being A (PK) + C (PK).  Therefore I want an after insert trigger on A to insert a row in B for each row in C.

Hope this makes sense!!!

Many thanks
0
Donoss
Asked:
Donoss
  • 4
  • 4
1 Solution
 
Nick UpsonPrincipal Operations EngineerCommented:
possible yes, but not necessary

in the trigger or SP, your choice

for select 1 from tableC do begin
  insert into tableB (whatever fields), values (whatever values)
end

or even more tidy, 1 statement

insert into tableB( whatever fields) select (whatever fields or constant values) from tableC;
0
 
DonossAuthor Commented:
Hi,

I need to set the primary key of table B to that of both table A and C so your second statement will not work, I have however tied the first statement but get a compile error at the 'Do'.  Code is as follows:

BEGIN
  for select
    REF_ID,
    QUESTION,
    COMMENTS
  from DSE_ASSESSMENT_QUESTION_LIST
  do begin
    insert into DSE_ASSESSMENT_QUESTION(
      DSE_ASSESSMENT_NBR,
      REF_ID,
      QUESTION,
      COMMENTS)
    values(
      DSE_ASSESSMENT_QUESTION.DSE_ASSESSMENT_NBR,
      DSE_ASSESSMENT_QUESTION_LIST.REF_ID,
      DSE_ASSESSMENT_QUESTION_LIST.QUESTION,
      DSE_ASSESSMENT_QUESTION_LIST.COMMENTS);
  END
END

Any help will be much appreciated.

Many thanks
0
 
DonossAuthor Commented:
The whole trigger is:

CREATE TRIGGER DSE_ASSESSMENT_AI FOR DSE_ASSESSMENT
ACTIVE AFTER INSERT
POSITION 11
AS
BEGIN
  for select
    REF_ID,
    QUESTION,
    COMMENTS
  from DSE_ASSESSMENT_QUESTION_LIST
  do begin
    insert into DSE_ASSESSMENT_QUESTION(
      DSE_ASSESSMENT_NBR,
      REF_ID,
      QUESTION,
      COMMENTS)
    values(
      DSE_ASSESSMENT_QUESTION.DSE_ASSESSMENT_NBR,
      DSE_ASSESSMENT_QUESTION_LIST.REF_ID,
      DSE_ASSESSMENT_QUESTION_LIST.QUESTION,
      DSE_ASSESSMENT_QUESTION_LIST.COMMENTS);
  END
END;

and I get the following compile error:

Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 11, column 3.
Do.


0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Nick UpsonPrincipal Operations EngineerCommented:
CREATE TRIGGER DSE_ASSESSMENT_AI FOR DSE_ASSESSMENT
ACTIVE AFTER INSERT
POSITION 11
AS
BEGIN
  for select
    REF_ID,
    QUESTION,
    COMMENTS
  from DSE_ASSESSMENT_QUESTION_LIST
into :a,:b,:c                                                                /* need to define these as well */
  do begin
    insert into DSE_ASSESSMENT_QUESTION(
      DSE_ASSESSMENT_NBR,
      REF_ID,
      QUESTION,
      COMMENTS)
    values(
      DSE_ASSESSMENT_QUESTION.DSE_ASSESSMENT_NBR,
      DSE_ASSESSMENT_QUESTION_LIST.REF_ID,
      DSE_ASSESSMENT_QUESTION_LIST.QUESTION,
      DSE_ASSESSMENT_QUESTION_LIST.COMMENTS);
  END
END;
0
 
DonossAuthor Commented:
Nick,

are a, b, and c local variables?  which aare then used in the insert?
0
 
Nick UpsonPrincipal Operations EngineerCommented:
yes, that's correct
0
 
DonossAuthor Commented:
Nick,

I managed to get it sorted using:

insert into DSE_ASSESSMENT_QUESTION (
    DSE_ASSESSMENT_NBR,
    REF_ID,
    QUESTION,
    COMMENTS)
  select
    new.DSE_ASSESSMENT_NBR,
    REF_ID,
    QUESTION,
    COMMENTS
  from DSE_ASSESSMENT_QUESTION_LIST;

I didn't try the other solution but you where right in your original post so I'll aware you all the points, many thanks for your help and immediate response.

0
 
Nick UpsonPrincipal Operations EngineerCommented:
yep, that is the most efficient as well
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now