INSERT/UPDATE Triggers

[jabhawk]

I am trying to implement Insert and update triggers in my application to handle auditing needs. Each table has a pair or date and userid fields showing create and last modified.

I want to use triggers to update these fields so that I can support both the UI and batch processing.

The trigger code is stored in the DBC as a Stored Procedure (ie. PROCEDURE _inser_tablename) and I call the code from the Update trigger of the table (ie _insert_tablename() )

For some reason the system keeps reporting that is "cannot update cursor" when I insert a new record (ie INSERT INTO TABLE (fields) VALUES (values) ). If I remove the trigger call the insert works fine.

Is there a good FAQ or whitepaper on implementing triggers like this? I can do this with no problems in other packages but VFP is not very clear.

Jon B

Sample trigger code:
PROCEDURE _table_insert_audit
SELECT table
REPLACE table.createdt WITH DATETIME(),;
table.createid WITH vCurrentUser,;
table.lastmoddt WITH DATETIME(),;
table.lastmodid WITH vCurrentUser


Jonthan A Black

 

[Mike Lewis]

Jon,

Are you saying that you want the trigger to update fields in the same table that the trigger is tied to? If so, you cannot do that.

If you could do that, you'd get into a loop. The trigger would cause an update, which would cause the trigger to fire, which would cause the update, .... etc. So VFP prevents you from doing it.

If I've misunderstood the question, please let me know.

Mike
Mike Lewis
Edinburgh, Scotland

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

 

[jabhawk]

Mike, Actually you are right on the button. I want to put the generic auditing logic into a common routine and have it fire when the update occurs.

I do not want to have to code the logic into every update process if I can get away with it.

If this is not possible in VFP then so be it. The documentation is not very clear when it discusses triggers and from the way I read it M$'s answer is "mabey".
Jon B Jonthan A Black

 

[Mike Lewis]

Jon,

You can put the logic into a single routine, and it can be called from a trigger The problem is that you cannot update the same table as the one that fires the trigger.

One possibility might simply be to maintain a separate table for the audit information.

Mike
Mike Lewis
Edinburgh, Scotland

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

 

[jabhawk]

Mike, It looks like I am going to try the best of both worlds. I am designing the system to manually call routines in a procedure library to update the UI based audits. Also I will add a trigger to write to a master log in another table for batch audits.

Between the two I can recreate the trail needed.

This might be extra work but it will meet my needs for now untill I can get a full DBMS like Oracle or Progress in place.

Jon B Jonthan A Black

 

[Mike Lewis]

Jon,

Hope it works. Come back if I can help further.

Mike
Mike Lewis
Edinburgh, Scotland

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