Need help Database trigger

Hi

I have been tasked to convert an old Clipper app to Foxpro Ver 9.0. The old software has 2 fields in all the databases one called ChangedOn and one called DeletedOn. These fields hold the date,time,computer name, and login name of the machine and user. These fields were updated anytime a record was changed or marked for deletion.

I would like to use the database triggers to update these fields because of the numerous places in the app that modify or delete records. I have created a procedure to modify this field in the record but I get an error that the cursor is read only. I am not sure what I am doing wrong. I would think this time date stamping of a record change is fairly common and would like a way to get this working or the best way to do this if it can not be done using the database triggers.

Thank you for any help you can give me.
Rob

Answer this question

Need help Database trigger

Hisham Jaber
Nice idea.
However it looks like it wouldn't work as is (typo I think) and:
1) Using a global variable doesn't sound to be feasible.
2) Adding sp_RuleField to fields should be cumbersome.

I thouched your procedure a bit if you don't mind (doesn't need gnMode and uses only sp_RuleRow() for row validation)

Procedure sp_RuleRow

If Empty(createdon)

   Replace createdon With Datetime()

Else

   If IsModified()

      Replace editedby With Sys(2015), editedon With Datetime()

   Endif

Endif

Endproc

Procedure IsModified

Local ix

For ix=1 To Fcount()

If Type(Field(m.ix)) # 'G' And ;

        OLDVAL(Field(m.ix)) # Evaluate(Field(m.ix))

        Return .T.

Endif

Endfor

Return .F.

Endproc
jceddy
Thank you for the help. We already had a custom tableupdate and delete routine and after a little sleep we figured out that was the best place for the code to go.

Thanks Again
Rob
Frederic BEAULIEU
VFP doesn't let same table changes via triggers. You might control it in your custom TableUpdate and Delete routines or separate those fields from the table itself. ie: An audit table might have fields like:
TableName, PrimaryKey, CreatedOn, ModifiedOn,DeletedOn

and you could update that via triggers.
Ianier
Rob,

You can also try the following.
Add the following stored procedures to the database and call the Sp_RuleRow stored procedure from the Record Validation Rule and the Sp_RuleField from the Field validation Rule.
then have a global variable as gnMode = 0. this will do the trick.

PROCEDURE sp_RuleRow

IF gnMode = 1
   REPLACE CreatedOn WITH DATETIME()

ELSE

   REPLACE editedby WITH SYS(2015), editedon WITH DATETIME()

ENDIF

gnMode = 0

RETURN

PROCEDURE sp_RuleField

   gnMode = 1

RETURN
Need help Database trigger

Answer this question