database trigger

[Robert Edis]

MS SQL Server has less overall functionality than any
Oracle RDBMS.  It does have table triggers, stored
procedures and formula columns.

In how many places can SSNUMS be modified?
If this column is NEVER modified, i.e. ony its
components SSNUMBER and STATUS are modifiable, then
using a calculated formula column in the table may be
best.  You may still need to refresh the data record
on the form after an update though.  If you can't
modify the table structure then use a standard stored
procedure that you can call from any form to update
the column.  Stored procedures are predictable and
visable to developers.

If the same columns are labeled differently in
different peices of code I suggest you "bite the
bullet" and introduce some standardisation now before
more time is wasted on fixing poorly written code.

Blue


--- Viet Nguyen <VNguyen@wsboces.org> wrote:

> The problem I have is that we have so many instances
> in qtp & quick that
> sometime refer to SSNUMR and other times refer to
> SSNUM or just STATUS
> alone. I was trying to avoid going to all of these
> millions lines of
> codes and have to logically & physically change
> them.
> 
> Do you know if MSSQL has better handling for this?
> 
> 
> -----Original Message-----
> From: powerh-l-admin@lists.sowder.com
> [mailto:powerh-l-admin@lists.sowder.com] On Behalf
> Of Robert Edis
> Sent: Wednesday, April 27, 2005 2:18 PM
> To: PowerHouse List
> Subject: RE: database trigger
> 
> Viet
> 
> Database table triggers are tricky and sometimes
> unpredictable.  They also represent "hidden" logic
> in
> that future maintenance programmers may be unaware
> of
> it when making changes to any code that touches that
> table.  I would avoid triggers unless they were the
> only option.
> 
> As Joe says doing the logic in a field process
> procedure is probably the best way to acheive what
> you
> want.
> 
> There are two other database options available to
> you
> as well but either may require a re-fetch of the
> data
> if SSNUMS is displayed on the PH form.  These are:
> 
> 1. a stored procedure run from the PH form
> 2. a calculated (formula) column in the database
> 
> That latter can never be modified as it is only the
> result of a calculation using other table columns.
> 
> Regards,
> 
> Blue
> 
> 
> --- Joe Boyle <atla38@dsl.pipex.com> wrote:
> 
> > if you do an update stay, you would have to modify
> > the PH code anyway
> > because the trigger will alter the row contents
> and
> > the checksum value for
> > subsequent puts. You would then have to do another
> > get to update the
> > checksum. You might as well add process procedures
> > on each of the two fields
> > to handle the changes.
> > 
> > Regards, Joe.
> > 
> > 
> > -----Original Message-----
> > From: powerh-l-admin@lists.sowder.com
> > [mailto:powerh-l-admin@lists.sowder.com] On Behalf
> > Of Viet Nguyen
> > Sent: 27 April 2005 14:46
> > To: powerh-l@lists.sowder.com
> > Subject: database trigger
> > 
> > Hi All,
> >    I am migrating c-isam to oracle db and I have a
> > question. 
> >    1) I have a field SSNUMS which has a
> substructure
> > SSNUMBER & STATUS.
> >    To eliminate modification to power code I am
> > thinking about using
> > trigger table trigger that will UPDATE the higher
> > structure SSNUMS when
> > there is an update is done on SSNUMBER or STATUS.
> > Could someone give me
> > some advise on how powerhouse handles the update
> or
> > how to create the
> > trigger so that it doesn't recursively call on
> > itself?
> > Thanks.
> > Viet.
> > 
> > 
> > -----Original Message-----
> > From: powerh-l-admin@lists.sowder.com
> > [mailto:powerh-l-admin@lists.sowder.com] On Behalf
> > Of Ken Langendock
> > Sent: Friday, April 22, 2005 11:32 AM
> > To: roger32909@bellsouth.net; Martin McDonough
> > Cc: powerh-l@lists.sowder.com
> > Subject: Re: RE: force a FIND?
> > 
> > Ok let me clarify this for you.
> > 
> > screen a
> > 
> > temp t-xyz-key char(??)
> > 
> > file XYZ primary
> > access via xyz-key usign t_xyz-key 
> > access via xyz-key request xyz-key
> > 
> > procedure x ; can be procedure entry/find/whatever
> > begin
> >   run screen b
> >   push find ; invokes once this procedure ends
> >   let t_xyz-key = xyz-key of XYZ
> > end
> > 
> > procedure path
> > begin
> >  if " " ne t-xyz-key
> >     then let path = 1
> >     else prompt xyz-key
> > end
> > 
> > procedure postfind
> > let t_xyz-key = " " ; clear it out for next find
> > 
> > 
> > 
> > If you want more clarification....send me the
> screen
> > code.
> > 
> > Ken
> > 
> > --- roger32909@bellsouth.net wrote:
> > > Already tried PUSH FIND.  This still required
> user
> > > intervention to Find the updated record.
> > > 
> > > REFRESH does not do a FIND.
> > > 
> > > The PRIMARY file XYZ record is NOT passed to the
> > > called screen.
> > > In the called screen, the file XYZ is a DESIGNER
> > > file. 
> > > 
> > > Agree with you, Martin, but unfortunately the
> > > subscreen is called from many other screens, so
> it
> > > is not possible to pass the record.
> > > 
> > > > 
> > > > From: "Martin McDonough"
> > > <martin.mcdonough@rosebys.com>
> > > > Date: 2005/04/22 Fri AM 10:17:25 EDT
> > > > To: <roger32909@bellsouth.net>
> > > > Subject: RE: force a FIND?
> > > > 
> > > > If possible, the correct way to do this would
> be
> > > to pass the primary
> > > > file 'XYZ' to the subscreen and display the
> > field
> > > from the 'passed' file
> > > > on the subscreen.
> > > > 
> > > > By putting 'REFRESH' on the field statement of
> > the
> > > main screen, after
> > > > the update is done in the subscreen and you
> > return
> > > to the main screen,
> > > > the value will be automatically refreshed.
> > > > 
> > > > Regards
> > > > 
> > > > Martin McDonough
> > > > 
> > > > -----Original Message-----
> > > > From: powerh-l-admin@lists.sowder.com
> > > > [mailto:powerh-l-admin@lists.sowder.com] On
> > Behalf
> > > Of
> > > > roger32909@bellsouth.net
> > > > Sent: 22 April 2005 15:07
> > > > To: powerh-l@lists.sowder.com
> > > > Subject: force a FIND?
> > > > 
> > > > OpenVMS 7.3  PH 7.10.G1
> > > > 
> > > > We have an old screen that works this way:
> > > > 
> > > > The PRIMARY file in the main screen is XYZ.
> 
=== message truncated ===