Trigger

How can I write a very simple piece of code in a delete trigger so that no
orphans arise? The referential integrity builder is too complex, and crashes
my VFP9 SP2.


Walter

Stefan
Wed Sep 19 00:22:24 PDT 2007


"WP" <wpaul@nospam_fibertel.com.ar> schrieb im Newsbeitrag
news:eZObRpm%23HHA.600@TK2MSFTNGP05.phx.gbl...
> How can I write a very simple piece of code in a delete trigger so that no
> orphans arise? The referential integrity builder is too complex, and
> crashes my VFP9 SP2.

Assumed you've got a parent table "parent" with a primary-key field
"parent_id" and child table "child" with "parent_id" as its foreign-key
field, you could create an SP like:

Procedure ri_parent_delete()
Local luPK
luPK = parent_id && no alias name here
Delete From child Where parent_id = m.luPK
EndProc


hth
-Stefan



--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

Dan
Wed Sep 19 08:49:48 PDT 2007

WP wrote:
> How can I write a very simple piece of code in a delete trigger so
> that no orphans arise? The referential integrity builder is too
> complex, and crashes my VFP9 SP2.
>
>
> Walter

You've reported the crash caused by the SP2 *BETA* haven't you?

Dan

WP
Fri Sep 21 13:39:22 PDT 2007

Thanks, Stefan,

I would prefer to show a message if there are children for the parent to be
deleted.
And then I have another question: normally in my programs I open the tables
with different aliases. Let's say I have the tables "parent" and "child".

In some programs I do:
USE parent ALIAS parent1 IN 1
USE child ALIAS child1 IN 2

but in some others I name the alias different:
USE parent ALIAS parent2 IN 1
USE child ALIAS child2 IN 2


Does your code work even in that cases ? Because I remember 2 weeks ago I
used an "UPDATE child SET...." command on the real table name, and the
changes were not buffered, but if I wrote "UPDATE child1 SET...." the
changes were buffered. So I concluded that you have to use in UPDATE and
DELETE commands the alias name, and not the real table name, in order for
the changes being buffered with the possibility to undo them should there be
an error some lines further down.

Walter



"Stefan Wuebbe" <stefan.wuebbe@gmx.de> escribió en el mensaje
news:%23HPmT3o%23HHA.1168@TK2MSFTNGP02.phx.gbl...
>
> "WP" <wpaul@nospam_fibertel.com.ar> schrieb im Newsbeitrag
> news:eZObRpm%23HHA.600@TK2MSFTNGP05.phx.gbl...
>> How can I write a very simple piece of code in a delete trigger so that
>> no orphans arise? The referential integrity builder is too complex, and
>> crashes my VFP9 SP2.
>
> Assumed you've got a parent table "parent" with a primary-key field
> "parent_id" and child table "child" with "parent_id" as its foreign-key
> field, you could create an SP like:
>
> Procedure ri_parent_delete()
> Local luPK
> luPK = parent_id && no alias name here
> Delete From child Where parent_id = m.luPK
> EndProc
>
>
> hth
> -Stefan
>
>
>
> --
> |\_/| ------ ProLib - programmers liberty -----------------
> (.. ) Our MVPs and MCPs make the Fox run....
> - / See us at www.prolib.de or www.AFPages.de
> -----------------------------------------------------------
>

Stefan
Sat Oct 13 03:34:05 PDT 2007

Hi Walter -
"WP" <wpaul@nospam_fibertel.com.ar> wrote in message
news:ePdM69I$HHA.1900@TK2MSFTNGP02.phx.gbl...
> Thanks, Stefan,
>
> I would prefer to show a message if there are children for the parent to
> be deleted.

I'd recommend not to do so in the stored procedure itself because
that way your formerly invisible "backend" gets a user interface and
cannot be used in scenarios like COM or web applications anymore.
Personally I'd prefer separate "tiers" and in this case write a few lines
of code in the UI tier to ask the user before the delete attempt would
take place.

> And then I have another question: normally in my programs I open the
> tables with different aliases. Let's say I have the tables "parent" and
> "child".
>
> In some programs I do:
> USE parent ALIAS parent1 IN 1
> USE child ALIAS child1 IN 2
>
> Does your code work even in that cases ?

Yes.

> Because I remember 2 weeks ago I used an "UPDATE child SET...." command on
> the real table name, and the changes were not buffered, but if I wrote
> "UPDATE child1 SET...." the changes were buffered. So I concluded that you
> have to use in UPDATE and DELETE commands the alias name, and not the real
> table name, in order for the changes being buffered with the possibility
> to undo them should there be an error some lines further down.

Yes, you're right - since you asked for "a very simple piece of code"... :-)
However, the previous sample works fine in a local-views-only scenario,
for instance.
OTOH, when the "child" table is in use with its default alias name *and is
buffered, the code might fail I think - in that case you can probably
improve it by opening the child table with a random alias inside the SP
and use that one for the Delete statement.

FWIW, for an RI alternative see also
http://www.utmag.com/ViewPageArticle.aspx?Session=7A39664C377966557263673D20683236326E43686B41656C49703477616A61314349673D3D


hth
-Stefan



--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------