Hi Steven,
Derby hews fairly closely to SQL Standard syntax. Your triggers look
wrong to me. Your triggered SQL statements are VALUES statements, which
simply manufacture some values and throw them into the void. I think
that is why you had to include MODE DB2SQL in your syntax. I don't think
that MODE DB2SQL causes Derby to actually behave like (some dialect of)
DB2 in this case. It just allows the syntax to compile both on Derby and
on the originating DB2 system.
See if the following alternative syntax gives you what you need:
CONNECT 'jdbc:derby:memory:db;create=true';
CREATE TABLE TEST1 ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS
IDENTITY(START WITH 1, INCREMENT BY 1 ) , UNIQUE_ID VARCHAR (47) NOT NULL ,
CREATETIME TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP, MODIFYTIME TIMESTAMP
, ENDTIME TIMESTAMP , STATUS NUMERIC (10) WITH DEFAULT 0 ); ALTER TABLE
TEST1 ADD CONSTRAINT TEST1_PK PRIMARY KEY ( ID ) ;
CREATE TRIGGER TEST1_BINS_TRG1 AFTER INSERT ON TEST1
REFERENCING NEW AS NEW FOR EACH ROW
UPDATE TEST1
SET CREATETIME = CURRENT_TIMESTAMP
WHERE ID = NEW.ID;
CREATE TRIGGER TEST1_BUPD_TRG1
AFTER UPDATE OF STATUS ON TEST1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
WHEN (NEW.STATUS >= 0 AND OLD.STATUS <> 9 )
UPDATE TEST1
SET STATUS = 9 , MODIFYTIME = CURRENT_TIMESTAMP, ENDTIME = CURRENT_TIMESTAMP
WHERE ID = OLD.ID;
-----------
INSERT INTO TEST1 (UNIQUE_ID) VALUES ('1');
SELECT * FROM TEST1;
UPDATE TEST1 SET STATUS=1 WHERE UNIQUE_ID='1';
SELECT * FROM TEST1;
Hope this helps,
-Rick
