help with update, insert, delete triggers for referential integrity needed 
Author Message
 help with update, insert, delete triggers for referential integrity needed

I'm pretty new to VFP (6).  Mostly just ever used what I knew from fox 2.6
for DOS and mixed it in with my VB experiences.  LOL.  Anyway, I decided to
delve into the dbc for this small conversion I'm doing.   I've got a routine
for reindexing and recreating the p-keys, foreign keys and such.  I
originally used the RI Builder to set up all the RI.  So the procedures are
there to run that.  But I can't seem to figure out how to code the reindex
routine to set up the triggers again so when I open the database designer I
can see the appropriate settings (restrict, update, cascade, ignore) in the
RI window.

assume a customer table where the primary key is CustID and a location table
where the foreign key is also CustID.  The following works:

ALTER TABLE location ADD FOREIGN KEY custid TAG custid REFERENCES customer
&& this works fine

I found the code for the delete trigger:
** "Referential integrity delete trigger for" customer
PROCEDURE __RI_DELETE_customer

But how do I CREATE TRIGGER......  in my reindex code in order to see this
properly in the RI window again?
(likewise for update and insert triggers)

The sampe code in the help:  CREATE TRIGGER ON customer FOR UPDATE AS
maxordamt <= 50
doesn't make sense to me here.  Shouldn't it refer to the SP that was
created earlier?
I understand the above as follows:  If an update occurs that fits the
expression "maxordamt <= 50" then if there is  an update trigger SP in the
SP code, then that code will fire.  Is it safe to assume that if you used a
dele trigger, then whatever the expression you have at the end of the CREATE
TRIGGER code (that of course has to evaluate to true or false) will fire the
delete SP code?  DO I have this right or not?

If I'm on the wrong track, don't yell at me.  Like I said, I'm new to this
part of foxpro.  Any help would be greatly appreciated.

Thanks :)

Keith



Thu, 05 Jan 2006 23:55:29 GMT  
 help with update, insert, delete triggers for referential integrity needed
Hi Keith,

Run C:\Program Files\Microsoft Visual Studio\Vfp98\Tools\Gendbc\GenDBC.prg
and look at the output it produces, also, go to www.stonefield.com and have
a look at what the Stonefield Database Toolkit can do for you.

Since you're writing a reindexing routine I have to ask why you think you
need to do this. Of course tables need to be reindexed when indexes have
gotten corrupted, but in general, the FoxPro data engine keeps indexes
up-to-date.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP



Quote:
> I'm pretty new to VFP (6).  Mostly just ever used what I knew from fox 2.6
> for DOS and mixed it in with my VB experiences.  LOL.  Anyway, I decided
to
> delve into the dbc for this small conversion I'm doing.   I've got a
routine
> for reindexing and recreating the p-keys, foreign keys and such.  I
> originally used the RI Builder to set up all the RI.  So the procedures
are
> there to run that.  But I can't seem to figure out how to code the reindex
> routine to set up the triggers again so when I open the database designer
I
> can see the appropriate settings (restrict, update, cascade, ignore) in
the
> RI window.

> assume a customer table where the primary key is CustID and a location
table
> where the foreign key is also CustID.  The following works:

> ALTER TABLE location ADD FOREIGN KEY custid TAG custid REFERENCES customer
> && this works fine

> I found the code for the delete trigger:
> ** "Referential integrity delete trigger for" customer
> PROCEDURE __RI_DELETE_customer

> But how do I CREATE TRIGGER......  in my reindex code in order to see this
> properly in the RI window again?
> (likewise for update and insert triggers)

> The sampe code in the help:  CREATE TRIGGER ON customer FOR UPDATE AS
> maxordamt <= 50
> doesn't make sense to me here.  Shouldn't it refer to the SP that was
> created earlier?
> I understand the above as follows:  If an update occurs that fits the
> expression "maxordamt <= 50" then if there is  an update trigger SP in the
> SP code, then that code will fire.  Is it safe to assume that if you used
a
> dele trigger, then whatever the expression you have at the end of the
CREATE
> TRIGGER code (that of course has to evaluate to true or false) will fire
the
> delete SP code?  DO I have this right or not?

> If I'm on the wrong track, don't yell at me.  Like I said, I'm new to this
> part of foxpro.  Any help would be greatly appreciated.



Fri, 06 Jan 2006 01:58:13 GMT  
 help with update, insert, delete triggers for referential integrity needed
Cindy,

Thanks for the GENDBC help.  I appreciate it.  Obviously the trigger
procedures that are run are really functions taht are returning .t. or .f.
which is what the create trigger is looking for.  I understand now.

The company I work for has a few VFP apps at various client offices.  I know
VFP6 is much better than the 2.6 versions as far as maintaining indexes, but
once in a while reindexing is necessary.  We've found that it's most
practical to delete the tags and rebuild them.  Sometimes just running REIN
doesn't work - at least not in our experience.

Thanks again,

Keith


Quote:
> Hi Keith,

> Run C:\Program Files\Microsoft Visual Studio\Vfp98\Tools\Gendbc\GenDBC.prg
> and look at the output it produces, also, go to www.stonefield.com and
have
> a look at what the Stonefield Database Toolkit can do for you.

> Since you're writing a reindexing routine I have to ask why you think you
> need to do this. Of course tables need to be reindexed when indexes have
> gotten corrupted, but in general, the FoxPro data engine keeps indexes
> up-to-date.

> --
> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP



> > I'm pretty new to VFP (6).  Mostly just ever used what I knew from fox
2.6
> > for DOS and mixed it in with my VB experiences.  LOL.  Anyway, I decided
> to
> > delve into the dbc for this small conversion I'm doing.   I've got a
> routine
> > for reindexing and recreating the p-keys, foreign keys and such.  I
> > originally used the RI Builder to set up all the RI.  So the procedures
> are
> > there to run that.  But I can't seem to figure out how to code the
reindex
> > routine to set up the triggers again so when I open the database
designer
> I
> > can see the appropriate settings (restrict, update, cascade, ignore) in
> the
> > RI window.

> > assume a customer table where the primary key is CustID and a location
> table
> > where the foreign key is also CustID.  The following works:

> > ALTER TABLE location ADD FOREIGN KEY custid TAG custid REFERENCES
customer
> > && this works fine

> > I found the code for the delete trigger:
> > ** "Referential integrity delete trigger for" customer
> > PROCEDURE __RI_DELETE_customer

> > But how do I CREATE TRIGGER......  in my reindex code in order to see
this
> > properly in the RI window again?
> > (likewise for update and insert triggers)

> > The sampe code in the help:  CREATE TRIGGER ON customer FOR UPDATE AS
> > maxordamt <= 50
> > doesn't make sense to me here.  Shouldn't it refer to the SP that was
> > created earlier?
> > I understand the above as follows:  If an update occurs that fits the
> > expression "maxordamt <= 50" then if there is  an update trigger SP in
the
> > SP code, then that code will fire.  Is it safe to assume that if you
used
> a
> > dele trigger, then whatever the expression you have at the end of the
> CREATE
> > TRIGGER code (that of course has to evaluate to true or false) will fire
> the
> > delete SP code?  DO I have this right or not?

> > If I'm on the wrong track, don't yell at me.  Like I said, I'm new to
this
> > part of foxpro.  Any help would be greatly appreciated.



Fri, 06 Jan 2006 03:11:32 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Added Referential Integrity - Now Trigger Fails

2. Help needed on Referential Integrity Builder

3. VFP5 : Referential Integrity DISAPPEARS With Delete Tag

4. Please Help on Sql Insert/Update/Delete

5. Please Help on Sql Insert/Update/Delete

6. Help: VFP 3.0b Problem with Data Environment and Referential Integrity

7. HELP, testing referential integrity VFP 5

8. insert, update triggers in VFP?

9. HELP HELP Auto number of VFP at insert trigger under multi-user environment

10. Update conflict while using delete-itself trigger

11. Views SQL Update or Delete then Insert.

12. Cannot Update, Insert or Delete (The Sequel)

 

 
Powered by phpBB® Forum Software