RE: SQL "SERIAL" field work around with a trigger.

["Andrew Lutz" <andrew.lutz@xxxxxxxxxxxx>]

Are you at V5R2?  Lose the trigger and use either:

        1. ROWID data type.
        2. IDENTITY column attribute.

If not use the age old proven method of the data area to handle the
sequence assignment.


-----Original Message-----
From: Mark Adkins [mailto:adkinsm@xxxxxxxxxx] 
Sent: Wednesday, April 30, 2003 3:12 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL "SERIAL" field work around with a trigger.


I want to set up a serial field that auto-increments when you insert a
new
record.  For a table that has two fields SEQUENCE BIGINT, DATTIM
TIMESTAMP.
If I insert/write the values (0, CURDATE) and the last sequence number
was
100, it would insert a record as 101.  I would then use my timestamp to
chain the record to get the sequence number created.  I didn't readily
find
a way to alter the record buffer on an RPG trigger, but an SQL trigger
did
the trick.

CREATE TRIGGER TransactionBeforeTrigger BEFORE INSERT ON TEST
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
BEGIN
  IF NEW_ROW.SEQ = 0 THEN
   SET NEW_ROW.SEQ =
       (SELECT IFNULL(MAX(SEQ),0) + 1 FROM TEST);
  END IF;
END

The problem I have is locking the table so only one process tries to
write
a seqence number at a time.  If I add, "LOCK TABLE TEST IN EXCLUSIVE
MODE
ALLOW READ;", it never releases the lock and eventually crashes.  Any
ideas?

Regards,

Mark

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.