Creating and using triggers 3

[Redsz]

Hello again. I have been attempting to create a trigger and still have had no success.

Is it possible to create a trigger that fires every time a replace statment is issued for that table? Meaning to have the trigger perfrom the following. Replace lsend with .t.

If it is how would i go about doing this as i have been receiving error messages all morning.

Thx in advance.

 

[Dan Freeman]

Creating triggers is pretty easy. Understanding what they can and can't do is a little tougher.

<i>Is it possible to create a trigger that fires every time a replace statment is issued for that table? </i>

Yes, this is pretty easy.

<i>Meaning to have the trigger perfrom the following. Replace lsend with .t.</i>

Everyone out of the pool! You can't have an update trigger update the current record because it would trigger the update trigger. It would cause a never-ending loop.

Why don't you tell us what your end-result needs to be? Perhaps someone will have an alternate suggestion.

 

[Redsz]

We are using the field lsend in all of the tables in our database to determine if the record needs to be sent to an offsite backup via a client server application. We have the field ( lsend ) being updated in all of the replace and insert statements in our application.

I was hoping to remove these by implementing a trigger of some sort, but as you have said this causes an infinite loop.

 

[craigsboyd]

Everyone out of the pool!

Redsz,

If you're going to do this you'll want to use a RULE, which is not hampered by the same thing that a trigger is...one of the places to try this without getting too crazy is a Record Validation rule...you'll have to place some code in a function that will do the replace and you'll want to make it heneric enough that it won't have to be rewritten for every table. My thoughts anyhow.

_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[darrellblackhawk]

Extending what Craig stated, I played around with a table's
record validation rules and came up with the code below.

It's not generic yet, but should be fairly easy to make
the needed changes to make it generic

It works propertly except for the condition described below.(*** Where it fails ***)

Unfortunately, it uses public variables that get defined
inside of the function which of course lend themselves to
maintenance problems.


*** Where it fails ***
If !b_lSend && Maintenance code has send the record to archive
Return .t.
Endif

Not sure yet how to get around the above.

The assumption is that whatever maintenance program exits
will send all records with lSend equal to True offsite and
after doing so will set lSend to False.

The problem arises when an existing record that has been sent to offsite archiving is updated later.

Since the field lSend will then be False, it doesn't get updated by the stored procedure.

Maybe another field can be added that is included in the test. I'll play a little more later.

Just a start.

Darrell

* I created a table in a DBC called test which has two fields:
* lSend b, Field1 c(10)

* In the default value for the field lSend, I placed .T.
* and in the table's record validation rule I placed the following:
* SetDefault(lsend,RECNO())

* In the stored procedures I created the following function:

Function SetDefault(b_lSend,nRecNo)

  * Note: This is a hack and probably doesn't need to be
  *       this complicated :-)

  If !b_lSend  && Maintenance code has send the record to archive
    Return .t.
  Endif

  If type("gaValidated")=="U"
    Public array gaValidated[1]
    gaValidated[1] = 0
    Public gnValidated
    gnValidated = 0
  Endif

  Local nValidatedRec, bAtZero
  nValidatedRec = ascan(gaValidated,nRecNo)

  If nValidatedRec == 0
    gnValidated = gnValidated + 1
    Dime gaValidated[gnValidated]
    gaValidated[gnValidated] = nRecNo
    Replace lSend with .t.
  Else
    =Adel(gaValidated,nValidatedRec)
    gnValidated = gnValidated -1

    If gnValidated == 0
      gnValidated = 1
      bAtZero = .t.
    Endif

    Dime gaValidated[gnValidated]
    If bAtZero
      gaValidated[gnValidated] = 0
    Endif
  Endif

  Return .t.
Endfunc

 

[craigsboyd]

This is what I came up with...run it from within a prg in VFP...remember you will need to clean up your C:\ drive afterwards cause it creates an actual dbf and dbc...I had to pretend the stored procedure a little by putting it right in with my code, you'd wanna put it in with the DBC in a production version, but this works pretty neat for the example anyways. Some buffering issues would need to be provided for but other than that this basic example I believe gives the desired effect.

CREATE DATABASE c:\test.dbc
CREATE TABLE c:\test.dbf (LastName c(30), FirstName c(30), lSend L DEFAULT .T., Check SetSend())
INSERT INTO test (LastName, FirstName) VALUES ("Craig", "Boyd")
INSERT INTO test (LastName, FirstName) VALUES ("John", "Smith")
INSERT INTO test (LastName, FirstName) VALUES ("Richard", "Green")
INSERT INTO test (LastName, FirstName) VALUES ("Don", "Wilson")
INSERT INTO test (LastName, FirstName) VALUES ("Sue", "Nelson")

*!* We should get 5 here because we just inserted 5 new records
=SendThem()
*!* Now let's do two replaces
GO TOP IN test
replace FirstName WITH "Joe"
SKIP 2
replace LastName WITH "Coolidge"
*!* Now we should get two
=SendThem()
*!* Now look in the table and see that all lsend's are false
BROWSE

FUNCTION SetSend()
	IF CURVAL("lSend") = lSend &&Don't do it if lSend is getting replaced
		replace lSend WITH .T.
	ENDIF
ENDFUNC

PROCEDURE SendThem
	*!* Just a made up procedure to mimick process
	LOCAL lnSentCounter
	lnSentCounter = 0
	SCAN ALL FOR lSend
		Replace lSend WITH .F.
		lnSentCounter = lnSentCounter + 1
	ENDSCAN
	MESSAGEBOX("Have just sent " + ALLTRIM(STR(lnSentCounter)) + " records")
ENDPROC

_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[darrellblackhawk]

Much cleaner Craig. Star to you.

Darrell

 

[craigsboyd]

Thank you for the star Darrell.

_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[Redsz]

I had some time to look your replies! Exactly what i was looking for. Thanks!

 

[Redsz]

Hi Craig.

The following code works great except on buffered tables. Sometimes the curval is returning null when inserting into a buffered table.

An append blank works correctly however.

What would you suggest in getting around this issue?

Cursorsetprop("buffering",5,alias())

FUNCTION setsend()
    IF CURVAL("lSend") = lSend &&Don't do it if lSend is getting replaced
        replace lSend WITH .T.
    ENDIF
ENDFUNC

 

[craigsboyd]

I would use ISNULLL() to check curval()'s return prior to checking it against lSend. Take the appropriate action is ISNULLL() returns true - I mean do you want it to be marked as Send or not? (rhetorical question)

_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​