Stored Procedure from trigger

Dev Shed Forums Sponsor:

February 2nd, 2007, 10:47 AM

FrankL

Registered User

Join Date: Jun 2006

Posts: 16

Time spent in forums: 2 h 32 m 30 sec
Reputation Power: 0

Stored Procedure from trigger

Hello,

I have this procedure body in the countsubjects(:in_book_id) procedure:

Code:

select count(*) from book_subjects where book_id = :in_book_id into :out_count; suspend;

I am trying to call it from an after insert trigger (if the new subject is the only subject for a particular book, it should be marked as primary).

What I want to do is:

Code:

o_count = execute procedure countsubjects(new.book_id);
if (o_count = 1) then 
  update book_subjects...

However, I received a parsing error when I attempted to compile the trigger (the procedure compiles and runs fine).

Code:

o_count = select * from countsubjects(new.book_id);
if (o_count = 1) then 
  update book_subjects...

Gives the same message: parsing error.

Any ideas on what I am doing wrong?

February 2nd, 2007, 03:48 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 855

Time spent in forums: 4 Days 11 h 11 m 43 sec
Reputation Power: 19

Quote:

Originally Posted by FrankL

Hello,

I have this procedure body in the countsubjects(:in_book_id) procedure:

Code:

select count(*) from book_subjects where book_id = :in_book_id into :out_count; suspend;

I am trying to call it from an after insert trigger (if the new subject is the only subject for a particular book, it should be marked as primary).

What I want to do is:

Code:

o_count = execute procedure countsubjects(new.book_id);
if (o_count = 1) then 
  update book_subjects...

However, I received a parsing error when I attempted to compile the trigger (the procedure compiles and runs fine).

Code:

o_count = select * from countsubjects(new.book_id);
if (o_count = 1) then 
  update book_subjects...

Gives the same message: parsing error.

Any ideas on what I am doing wrong?

What follows is meant to be helpful, not critical.
Please read it that way.

First: It is hard to suggest where your parsing error occurs without the full code.

Second: You may be taking a poor approach to the problem.

What happens if another subject for the book is entered later,
or by someone else at the same time?

Does the primary designation go away or possibly move to
another record on adding a new subject?

If primary always refers to the first entry, then a TIMESTAMP field will
probably fill your needs.
If not you may be better served dealing with all the logic through
stored procedures that your applications will have to take
responsibility for calling as appropriate.

Clive.

Comments on this post

pabloj agrees!

February 2nd, 2007, 04:48 PM

FrankL

Registered User

Join Date: Jun 2006

Posts: 16

Time spent in forums: 2 h 32 m 30 sec
Reputation Power: 0

I don't take it critically at all. I'll answer your questions then post the code.

Quote:

What happens if another subject for the book is entered later, or by someone else at the same time?

If another subject is entered later, nothing changes in the Is_Primary field. If entered by someone else at the same time, that would depend on which computer gets their instructions to the server first, I believe. The trigger is server-side, correct?

Quote:

Does the primary designation go away or possibly move to another record on adding a new subject?

No. A new subject tied to the book will not change a prior setting of primary. Primary can be changed by the user's choice. When changed, there is another trigger that sets the former primary to false.

Quote:

If primary always refers to the first entry, then a TIMESTAMP field will
probably fill your needs.

No. Primary means the main subject matter of the book. A book may be under the subjects Delphi, C++, and Firebird. Primary would be whichever the user decides is most central to the book.

Quote:

If not you may be better served dealing with all the logic through
stored procedures that your applications will have to take
responsibility for calling as appropriate.

I think that's what I'm trying to do here. I have a trigger that needs to call a stored procedure.

Here's the code that keeps bombing. I'll just list the trigger because the procedure compiles and runs just fine.

The trigger is an after insert trigger on the table book_subject. It is position 1.

Code:

as
declare variable ot_count integer;
begin
  ot_count = execute procedure countsubjects(new.book_id);
  if (ot_count = 1) then
    update book_subject
    set book_subjeect.is_priimary = 1
    where book_subject_id = new.book_subject_id;
end

IBExpert keeps marking the first line after the begin with "parser error."

Thank you.

February 2nd, 2007, 06:06 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 855

Time spent in forums: 4 Days 11 h 11 m 43 sec
Reputation Power: 19

Quote:

Originally Posted by FrankL

Here's the code that keeps bombing. I'll just list the trigger because the procedure compiles and runs just fine.

The trigger is an after insert trigger on the table book_subject. It is position 1.

Code:

as
declare variable ot_count integer;
begin
  ot_count = execute procedure countsubjects(new.book_id);
  if (ot_count = 1) then
    update book_subject
    set book_subjeect.is_priimary = 1
    where book_subject_id = new.book_subject_id;
end

IBExpert keeps marking the first line after the begin with "parser error."

Thank you.

First: there are a couple of spelling errors, don't know if they are in your original code. Double e and double i in book_subjeect.is_priimary

Second: It is better to be consistent with the use of table names and aliases. Either use them throughout or not at all (only if no joins, of course).

Third try:

Code:

as
declare variable ot_count integer;
begin
  select :out_count from countsubjects(new.book_id) 
  into :ot_count;
  if (ot_count = 1) then
    update book_subject
    set book_subject.is_primary = 1
    where book_subject.book_subject_id = new.book_subject_id;
end

NOTE: The variable out_count needs to be exactly as you declared it as the out parameter of the stored procedure.

Hope this helps,
Clive

February 5th, 2007, 10:02 AM

FrankL

Registered User

Join Date: Jun 2006

Posts: 16

Time spent in forums: 2 h 32 m 30 sec
Reputation Power: 0

Clive,

Thanks for your help. The line

Code:

select out_count from countsubjects(new.book_id)
into :ot_count

did the trick. No colon on the var after select is needed, but it works.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: