chas@sfc.Wichita.NCR.COM (Charles Binford) (02/06/90)
I am running Oracle 5.1 and have a question on how to perform something in my form. I have a key-nxtfld trigger on a field that sometimes updates another display-only field. The field update should *never* happen when the record has been queried-up (?) and is being changed (UPDATED). If this is a new record (INSERT), then based on other criteria, I want to modify the display-only field. How do I know if I am in UPDATE or INSERT mode? I have thought about a trigger on the query key which would set a global.variable. But, what about when the user's query returns 5 records, he arrows down to the 6th (blank) record and *inserts* a new record. What trigger can I set to tell me this is an insert and not an update? My ideal solution would be to access a 'system.mode' variable that told me if the current record was either an update or an insert. I do not know of any such variable, however :-(. Thanks for any help. Charles Binford, Automation Engineering, NCR E&M Wichita <C.Binford@Wichita.NCR.COM> <{ece-csc,hubcap,gould,rtech}!ncrcae!ncrwic!c.binford> <{sdcsvax,cbatt,dcdwest,nosc.ARPA,ihnp4}!ncr-sd!ncrwic!c.binford>
jalsop@seachg.UUCP (John Alsop) (02/07/90)
In article <326@sfc.Wichita.NCR.COM> chas@sfc.Wichita.NCR.COM (Charles Binford) writes: >I am running Oracle 5.1 and have a question on how to perform something >in my form. > >How do I know if I am in UPDATE or INSERT mode? I have thought about >a trigger on the query key which would set a global.variable. But, what >about when the user's query returns 5 records, he arrows down to the >6th (blank) record and *inserts* a new record. What trigger can I set >to tell me this is an insert and not an update? > >Charles Binford, Automation Engineering, NCR E&M Wichita In a similar situation, I distinguished between the two cases by checking whether one of the database fields was empty. It woud be non-empty if the record has been retrieved from the database, and would be empty if it had been cleared by the KEY-CREREC operation. -- John Alsop Sea Change Corporation 1100 Central Parkway W., Suite 38 Mississauga, Ontario, Canada L5C 4E5 Tel: 416-272-3881 Fax: 416-272-1555 UUCP: ...!uunet!attcan!darkover!seachg!jalsop -- John Alsop Sea Change Corporation 1100 Central Parkway W., Suite 38 Mississauga, Ontario, Canada L5C 4E5 Tel: 416-272-3881 Fax: 416-272-1555 UUCP: ...!uunet!attcan!darkover!seachg!jalsop
mouser@portia.Stanford.EDU (Michael Wang) (02/07/90)
In article <326@sfc.Wichita.NCR.COM> chas@sfc.Wichita.NCR.COM (Charles Binford) writes: > I have a key-nxtfld trigger on a field that sometimes updates another > display-only field. The field update should *never* happen when the > record has been queried-up (?) and is being changed (UPDATED). > If this is a new record (INSERT), then based on other criteria, > I want to modify the display-only field. > ... > My ideal solution would be to access a 'system.mode' variable that > told me if the current record was either an update or an insert. I > do not know of any such variable, however :-(. Probably the easiest way to do this is to check the rowid of the row that you are in. If the rowid is NULL, then you know that the row is being inserted, otherwise you know the row is being updated. For example you could have a KEY-NXTFLD trigger that looked something like: #EXEMACRO CASE block.rowid IS WHEN '' THEN COPY :field1 INTO :field2; NXTFLD; WHEN OTHERS THEN NXTFLD; END CASE; The COPY INTO command is a new feature in SQL*Forms 2.3. If you are using SQL*Forms 2.0, then you would have to use success/failure labels and branch to different steps. -Michael Wang +--------------+------------------------------------------------------------+ | Michael Wang | 325 Melville Avenue, Palo Alto, CA 94301 | |--------------+------------------------------------------------------------| | ARPAnet, CSNET, BITNET, Internet: mouser@portia.stanford.edu | | UUCP: ...decwrl!portia.stanford.edu!mouser | +---------------------------------------------------------------------------+
tgreenla@oracle.uucp (Terry Greenlaw) (02/10/90)
In article <8921@portia.Stanford.EDU> mouser@portia.Stanford.EDU (Michael Wang) writes: >In article <326@sfc.Wichita.NCR.COM> >chas@sfc.Wichita.NCR.COM (Charles Binford) writes: > >> I have a key-nxtfld trigger on a field that sometimes updates another >> display-only field. The field update should *never* happen when the >> record has been queried-up (?) and is being changed (UPDATED). >> If this is a new record (INSERT), then based on other criteria, >> I want to modify the display-only field. >> ... >> My ideal solution would be to access a 'system.mode' variable that >> told me if the current record was either an update or an insert. I >> do not know of any such variable, however :-(. > > >Probably the easiest way to do this is to check the rowid of the row >that you are in. If the rowid is NULL, then you know that the row is >being inserted, otherwise you know the row is being updated. > ...trigger example deleted for brevity... >-Michael Wang > The rowid check is the best way I know of in SQL*Forms versions before 3.0. If you have version 3.0 of SQL*Forms, there is a system variable called SYSTEM.RECORD_STATUS that contains the record status (New, Queried, Changed,etc.) of the record the cursor is currently in. However, the rowid test will still also work with version 3.0. Good Luck, Terry O. Greenlaw Sheathed within the Walkman, Staff Engineer Wear a halo of distortion. Oracle Corporation Aural contraceptive, tgreenla@oracle.oracle.com Aborting pregnant conversation - Marillion
chas@sfc.Wichita.NCR.COM (Charles Binford) (02/12/90)
In article <1990Feb9.202005.2307@oracle.com> tgreenla@oracle.uucp (Terry Greenlaw) writes: > The rowid check is the best way I know of in SQL*Forms versions >before 3.0. If you have version 3.0 of SQL*Forms, there is a system variable >called SYSTEM.RECORD_STATUS that contains the record status (New, Queried, >Changed,etc.) of the record the cursor is currently in. However, the rowid >test will still also work with version 3.0. Good Luck, Thanks for all of the help. Testing the rowid for null, not null did the trick for me (I'm on version 2.0). -- Charles Binford, Automation Engineering, NCR PPD Wichita <C.Binford@Wichita.NCR.COM> <uunet!ncrlnk!ncrwic!c.binford>