Autoincrementing Temp-Tables

[KMoody]

Is it possible to automatically increment a field in a temp-table whenever you create a new record? In normal tables, you have to use a trigger, so can you use triggers with temp-tables, too?

 

[TomBascom]

Maybe.

Sort of.

I haven't found any actual sample code and I'm a bit tied up at the moment but I believe that you might be able to get what you want by using the ROW-CREATE event of a ProDataSet (just create a PDS with a single TT in it...) The documentation is "sparse" on the topic but apparently you use SET-CALLBACK-PROCEDURE() to link the event to the trigger code.

It also apparently won't fire unless TRACKING-CHANGES is set to yes.

 

[KMoody]

Thanks, Tom. This is what I tried:

define variable hTest  as handle no-undo.

DEFINE TEMP-TABLE test BEFORE-TABLE before-table-name
    FIELD columnNum   AS INTEGER
    FIELD description LIKE ITEM.DESCRIPTION
    FIELD prod-code   LIKE PRODMSTR.PROD-CODE
    INDEX myIndex IS UNIQUE columnNum ASCENDING.
  
DEFINE DATASET ds-1
   FOR test.
  
hTest = TEMP-TABLE test:HANDLE.
hTest:TRACKING-CHANGES = TRUE.

DEFINE VARIABLE uniqueColumn AS INTEGER INIT 1.

hTest:SET-CALLBACK-PROCEDURE("ROW-CREATE","increment").

PROCEDURE increment:
    test.columnNum = uniqueColumn.
    uniqueColumn = uniqueColumn + 1.
END PROCEDURE.

However, I got this error:
**SET-CALLBACK-PROCEDURE is not a queryable attribute for TEMP-TABLE widget. (4052)

Why does it think I'm trying to do a query?

 

[TomBascom]

I think you need to be using the dataset handle not the TT handle.

 

[TomBascom]

"queryable" means you cannot read the attribute (because TT's have no such attribute). Not that you are trying to do a query in the "for each" sense.

 

[KMoody]

I think I figured it out! Here's the code:

DEFINE VARIABLE hTest AS HANDLE NO-UNDO.
DEFINE TEMP-TABLE test BEFORE-TABLE before-table-name
  FIELD columnNum AS INTEGER
  FIELD prod-code LIKE PRODMSTR.PROD-CODE
  INDEX myIndex IS UNIQUE columnNum ASCENDING.

DEFINE BUFFER bTest FOR TEMP-TABLE test.
DEFINE DATASET dSet FOR bTest.
DEFINE VARIABLE uniqueColumn AS INTEGER INIT 1.


/* Create a handle so we can set the temp-table's TRACKING-CHANGES to true */
hTest = TEMP-TABLE test:HANDLE.
hTest:TRACKING-CHANGES = TRUE.

/* Use the temp-table's buffer to set a callback procedure. */
BUFFER bTest:SET-CALLBACK-PROCEDURE ("ROW-CREATE","increment").

/* Callback procedure */
PROCEDURE increment:
  DEFINE INPUT PARAMETER DATASET FOR dSet.
  bTest.columnNum = uniqueColumn.
  uniqueColumn = uniqueColumn + 1.
END PROCEDURE.

/* Populate the temp-table using its buffer */
FOR EACH PRODMSTR WHERE PRODMSTR.DISC-INDEX > 0 AND PRODMSTR.DISC-INDEX < 5 NO-LOCK:
  CREATE bTest.
  bTest.prod-code = PRODMSTR.PROD-CODE.
END.

FOR EACH test NO-LOCK:
  DISPLAY test.
END.


PAUSE.
QUIT.

And here's the output:

columnNum PRODUCT CODE
---------- ------------
  1 30101
  2 30111
  3 30240
  4 30303
  5 30304
  6 30305
  7 30306
  8 30307
  9 30308
  10 30309
...

Not exactly elegant, but it gets the job done.

I wonder, though, why we're required to pass a dataset to our callback procedure, even if we don't need it. And why do we have to use a temp-table buffer? (Bear in mind that I'm new to buffers, handles, and datasets.)

 

[TomBascom]

For that matter why do we need yet another way to define a trigger?

The left hand obviously wasn't talking to the right hand is all I can think of.

All's well that ends well -- you've got what you need

 

[tamhas]

Just to stir things around a bit, I have never liked updating things with triggers because it means that the logic responsible for the contents of a record is in two different places. I have the same objection here ... perhaps in a program this small one can see the whole thing, but it would be very easy to have the callback procedure and the rest of the update separated and thus harder to see. Myself, I find it plenty easy to just stick the assign and increment logic in with the rest of the assignments creating the record and then it is very clear and simple.