Expected behavior of user-defined table triggers?

[Datadis]

Hi there,
I've got to redefine the update and delete triggers on an app I'm working on. I want to enable user-confirmed cascading deletion on some tables and archiving on another.

(I can't use the built-in cascading because I'm making a French application with an English version of VFP, and I want to offer the user the possibility to back-off when he realises the number of deletions concerned for a parent record deletion, with French dialogs...)

Now the problem isn't with the coding; I know I can cope with that. I just don't know what is expected from those table triggers, i.e.: are they supposed to just validate if the operation is possible, then return the result as a boolean and let VFP delete the concerned record(Do I put a "DELETE" statement in the trigger or just return the result of the validation)? Am I supposed to have the triggers handle the modification/deletion or just tell VFP whether it's OK or not to proceed?

Another thing, where is stored the database path in the Registry entries of a deployed application? My application will be installed on several PCs with a single instance of the DB on a shared directory, but there's is no server involved.

Thank you all,
Pascal Marier-Dionne,
Programmer

 

[Mike Lewis]

Pascal,

I just don't know what is expected from those table triggers, i.e.: are they supposed to just validate if the operation is possible, then return the result as a boolean and let VFP delete the concerned record ...

The answer is that a trigger can do anything you want it to do. Although they are commonly used to prevent a deletion where that would break referential integrity, you are completely free to write whatever code you like in a trigger.

If you want to cascade the deletes, you can do so. If you want to prompt the user for permission to cascade the deletes, that's OK too.

There are a couple of things to watch out for:

- The trigger shouldn't perform the action that invokes triggers, on the same table. For example, an update trigger on the customer table should not update the customer table (more recent versions of VFP will trap any attempt to do this, but earlier ones will just crash).

- If there's any chance that the trigger will fire as result of a call from ODBC or OLE DB, you've got to make sure that the caller can support a user interface. For example, if you have code running on a web server, and if that code does something to invoke the trigger, then the trigger should't do any user interface operation.

I hope this helps. Re your question about registry paths, I suggest you start a new thread for that, with a meaningful title. That way, anyone interested in that question will see it more easily.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Olaf Doschke]

Hi Pascal,

I would not put the logic into the delete or update triggers of the DBC. I would simply select count(*) the records related to the one record the user wants to delete or update before doing so and then this whole logic is application side business logic. The database then should simply cascade the deletes or updates.

Bye, Olaf.

 

[Datadis]

Thanks, both of you (again)
So just to resume; the triggers aren't supposed to return a confirm value (.T./.F.) and shouldn't issue the DELETE command?

Pascal Marier-Dionne

 

[Mike Lewis]

Pascal,

the triggers aren't supposed to return a confirm value (.T./.F.) and shouldn't issue the DELETE command?

No. A trigger must return a logical value. If the value is .T., the triggering action (the UPDATE in your case) will be accepted, otherwise it will be rejected.

I didn't say you shouldn't put DELETE in a trigger. My point was that you wouldn't put an UPDATE in an UPDATE trigger (on the same table), and you wouldn't put a DELETE in a DELETE trigger (on the same table). You can put DELETE in an UPDATE trigger.

Olaf made a good point when he said not to use triggers at all. It's much simpler to do the referential integrity checks in your application's code, and that's what I would usually do myself. On the other hand, the advantage of a trigger is that it centralises the code. If you want to alter your referential integrity processing, you only need to do it one place.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Olaf Doschke]

Hi Mike and Pascal,

while centralization may be a good point I'd rather not put interactive code or message boxes in triggers, or else you won't be able to use this DBC remotely by ODBC or OLE DB Provider. Don't say, that you won't need this - that day may come...

Simply refuse to delete (or update) by returning .F. in your user defined trigger. This will trigger error 1539, which you may handle in your application by showing your message. You can use a public variable like PUBLIC gcRIError to denote what has caused the trigger failure, as error 1539 does not differtiate if the error was caused by the standard code or user defined code, as in the end the standard referential code is also user defined...

At least AERROR() will give you one additional information: which trigger (insert/update/delete) caused the error. See the 5th element of the array created by AERROR after an error 1539 occurred.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

I more-or-less agree with you -- I made the same point earlier about executing the trigger remotely via ODBC or OLE DB.

There are some situations where triggers are really useful, but in general I prefer to place these sort of checks in my application code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)