Take the following DDL:

------------------------------------------------
-- DDL Statements for table "TEST    "."MEMOS"
------------------------------------------------


CREATE TABLE "TEST    "."MEMOS"  (
         "BRCH" DECIMAL(3,0) NOT NULL ,
         "ACCT" DECIMAL(7,0) NOT NULL ,
         "DATE" DATE NOT NULL ,
         "SEQ_NBR" INTEGER NOT NULL ,
         "TIME" TIME NOT NULL ,
         "TEXT" VARCHAR(255) NOT NULL )  
        IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "TEST    "."MEMOS"

ALTER TABLE "TEST    "."MEMOS"
    ADD CONSTRAINT "MEMOS_PK" PRIMARY KEY
        ("BRCH",
        "ACCT",
        "DATE",
        "SEQ_NBR");

And the following before trigger

CREATE TRIGGER test.memo_seq
      NO CASCADE BEFORE INSERT ON test.memos
      REFERENCING NEW AS n
      FOR EACH ROW
      MODE DB2SQL
BEGIN ATOMIC
 SET n.date = CURRENT DATE;--
 SET n.time = CURRENT TIME;--
 SET n.seq_nbr = (
   SELECT
     CASE
       WHEN max_seq IS NULL THEN 1
       ELSE max_seq + 1
     END AS next_seq
   FROM (
     SELECT MAX(memos.seq_nbr) AS MAX_SEQ
     FROM test.memos AS memos
     WHERE memos.brch = n.brch
       AND memos.acct = n.acct
       AND memos.date = CURRENT DATE
   )
 );--
END;

The goal, and this works, is that the first memo row for any branch/account
for any particular day will be automatically assigned SEQ_NBR of 1.  After
that each row inserted for the same branch/account will be assigned the
number following the highest SEQ_NBR value.

This works if I insert one row at a time, ie:

DELETE FROM test.memos;
INSERT INTO test.memos (BRCH, ACCT, TEXT)
 VALUES (543, 2870819, 'This is a memo!');
INSERT INTO test.memos (BRCH, ACCT, TEXT)
 VALUES (543, 2870819, 'This is another memo!!');
INSERT INTO test.memos (BRCH, ACCT, TEXT)
 VALUES (543, 2870819, 'This is another memo!!!');
DELETE FROM test.memos
 WHERE brch = 543  
   AND acct = 2870819
   AND seq_nbr = 2;
INSERT INTO test.memos (BRCH, ACCT, TEXT)
 VALUES (543, 2870819, 'This is another memo!!!!');
SELECT *
 FROM test.memos;
DELETE FROM test.memos
 WHERE brch = 543  
   AND acct = 2870819
   AND seq_nbr = 4;
INSERT INTO test.memos (BRCH, ACCT, TEXT)
 VALUES (543, 2870819, 'This is another memo!!!!!');
SELECT *
 FROM test.memos;

BRCH  ACCT      DATE       SEQ_NBR     TIME     TEXT                        
                                                                           
                                                                           
                                                                       
----- --------- ---------- ----------- -------- -------------------------
543.  2870819. 03/28/2008           1 14:36:07 This is a memo!
543.  2870819. 03/28/2008           3 14:36:07 This is another memo!!!
543.  2870819. 03/28/2008           4 14:36:07 This is another memo!!!!

 3 record(s) selected.

BRCH  ACCT      DATE       SEQ_NBR     TIME     TEXT                        
                                                                           
                                                                           
                                                                       
----- --------- ---------- ----------- -------- -------------------------
543.  2870819. 03/28/2008           1 14:36:07 This is a memo!
543.  2870819. 03/28/2008           3 14:36:07 This is another memo!!!
543.  2870819. 03/28/2008           4 14:36:07 This is another memo!!!!!

 3 record(s) selected.

However it doesn't like this:

INSERT INTO test.memos (BRCH, ACCT, TEXT)
VALUES (543, 2870819, 'This is a memo!')
    , (543, 2870819, 'This is another memo!!')
    , (543, 2870819, 'This is another memo!!!');

SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains

table "TEST.MEMOS" from having duplicate values for the index key.  
SQLSTATE=23505

I am guessing it's because the result of
SELECT MAX(memos.seq_nbr) AS MAX_SEQ
     FROM test.memos AS memos
     WHERE memos.brch = n.brch
       AND memos.acct = n.acct
       AND memos.date = CURRENT DATE

is the same in all three cases because the first row hasn't actually been
inserted when the trigger is fired for the second row.

Any thoughts how to make this work?

Any other thoughts about the entire process?

Thanks,
Frank