stored procedures and triggers

[OhioBill]

I’m using VFP 8 sp 1 on Windows 2000 pro.

I have a database and a table within the DBC with the following structure:

cost N(4,2)
price N(4,2)
profit N(4,2)

since profit is price minus cost, I’d like to automatically update the field profit whenever cost or price change. I’ve tried using triggers insert and update with stored procedures, but when I try and replace profit with some value, I get the error “Cannot update the cursor <filename> since it is read only”

What am I missing? Is there a way to do this?

 

[Mike Gagnon]

I’ve tried using triggers insert and update with stored procedures,

This is not the right place to do this. If you are using VFP8.0, use the database events.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[OhioBill]

Mike:

I'm using VFP 8 sp 1, and I looked at database events but I can't seem to find the event that fires after a record is updated. I must be looking past it somehow. Which event do you suggest?

 

[rgbean]

Bill,
Database Events have to do with changes to the database (tables, views, relationship, connections, etc.) not to the individual records in a table. There are three basic table events - Insert, Delete and Update - these are refered to as Triggers. One of the rules of triggers is that they can't change the data in the record that's being changed! (This could cause a situation of an update, causing an update, causing an update, etc.!)

Good database design NEVER has a field dependent on data in the same row (record). You can always calculate the value as you need it from the other fields.

While you could have the field validation rules (for cost and price) in the table update the profit field it's really not recommended.

Rick

 

[Mike Lewis]

OhioBill,

There are two points here:

First, you cannot use a trigger to update the record that fires the update trigger. If you could, the trigger would fire the trigger, which would fire the trigger, until the poor old fox drops dead with exhaustion.

Secondly, I would suggest that you simply drop the profit field. Any time your application needs to know the profit, just do the calculation on the fly. Not only is it good database design, as Rick rightly pointed out, but it is also easier and possibly more efficient.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[OhioBill]

Rick, Mike,

Thanks. I'm dropping the field as you suggest. Thanks again for your time and help.

Bill