Changing data using Update Trigger? 2

[pt777]

Is it possible to change table data using an Update Trigger or similar record validation in 'MODIFY STRUCTURE'.

When I call on a UDF that replaces a record in a field I get: "Trigger failed: table is read only". Of course this makes sense since changing the record would become nested.

I suppose I must always use buffered fields and forms to do it.

Thanks,

Philip

 

[Mike Lewis]

Philip,

Is it possible to change table data using an Update Trigger or similar record validation in 'MODIFY STRUCTURE'

Could you clarify this? It doesn't really make a lot of sense.

MODIFY STRUCTURE is not used to update data; it is used to change the underlying table structure. If you are asking if modifying the structure will fire a trigger, the answer is no.

If your trigger attempts to update the same table that fires the trigger, the result depends on which version of VFP you using. In 5.0 and earlier, the results were unpredicatble. In later version, you will get an error message, but I don't think it is the one you mentioned.

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

 

[pt777]

Basically I'm trying to CHANGE unbuffered table data using Trigger Validation.

I'm beginning to realize this is NOT possible as the data-tables themselves seem to become 'read-only' during trigger events.

Specifically I'm trying to consistently and programatically replace a datetime() value into a field: ...called ModificationTime ... (i.e., whenever that table's record is updated).

As always, thanks for your input,

Philip

 

[Mike Lewis]

Philip,

A trigger can update a table, but not if it is the same table as the one that fires the trigger.

Logging the time of an update is quite common, but if you want to store the time in the record you are actually updating, you won't be able to use a trigger to do so.

But you haven't explained where MODIFY STRUCTURE comes into the picture.

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

 

[pt777]

Please disregard my first sentence (poor grammar) about 'Modify structure'. I merely stated that command to help illustrate where I accessed triggers (vs., say, the RI triggers in 'Data Environment'). (Again my VFP grammar is poor)

As you are quite aware, though, I am trying to Log the time of each record's update consistently and accurately
(...i.e., for 'handshaking' with a Outlook COM object that is named oCalendarItem.LastModificationTime)

Based on what you stated, I may entertain ideas to 'update trigger' a child table of 'mytable' to log record updates, perhaps even a 'mytable_a' table (same table) if that's even possible.

Or I'll continue to experiment with buffered data in form field events. (This has been tricky)

Thanks, Philip

 

[Mike Lewis]

Philip,

I may entertain ideas to 'update trigger' a child table of 'mytable' to log record updates, perhaps even a 'mytable_a' table (same table) if that's even possible.


I think the usual approach is to have a separate table dedicated to holding timestamps. It can hold timestamps for all the tables in the database.

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

 

[pt777]

Very well, thank you for broadening my logic in this matter.

Yet, methinks, to have one seperate table as a child table (e.g., timestamp.dbf) for every table in the database would require an indexed field for each work area ... for parent RI triggers (or such) to work.

Such a table would have numerous records, yes?

Philip

 

[Mike Lewis]

Philip,

Such a table would have numerous records, yes?

It depends. Do you need to timestamp every update? If so, yes, the table will become very large over time. But maybe it will be enough to timestamp the most recent update (for each table). In that case, the timestamp table only needs one record for each main table that you are updating.

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

 

[wsobers]

I'm coming to this late, but fox has a LUPDATE() function which gives that date of the last update. Also check the 10 minute solution at:
"

http://gethelp.devx.com/techtips/vfox_pro/10min/10min0899.asp"

Wayne

 

[Mike Lewis]

Wayne,

has a LUPDATE() function which gives that date of the last update[/u]

That's a good point. However, I don't think it would work for Philip. That's partly because it only gives the date, not the time, and partly because it requires the table to be open. Also, I imagine Philip needs to record additional information, like the ID of the user carrying out the update.

Still, LUPDATE() is useful in some cases, and worth keeping in mind.

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

 

[pt777]

Thanks Wayne for your penetrating thoughts. I looked at your excellent references. I also checked out the fdate() which returns datetime() updates, which may help me with non-buffered table updates.

A problem with fdate() is it seems to require closing a table (not a database) before it returns the datetime() update. Mike is right, that the update field contains an unique ID as well (of the record, not the user).

Again, thank you both for your insightful feedback,

Philip