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

Mark,

Other suggestions aside, I think you need to do a commit to release the lock.

Matt

-----Original Message-----
From: Mark Adkins [mailto:adkinsm@xxxxxxxxxx]
Sent: Wednesday, April 30, 2003 6: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.