Hi Everyone,
I have several triggers I used in DB2 LUW, DB2 zOS, Oracle DB and MS SQL
Server that set timestamp columns before inserts and before updates. When
trying to do something similar in DerbyDB and use CURRENT_TIMESAMP the
triggers deploy but at run time I get issues/errors detailed below.
Any idea what I am doing wrong or are BEFORE triggers not allowed to use
CURRENT_TIMESTAMP.
Details:
Apache Derby version 10.14.2.0 .
Example deployment:
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 NO CASCADE BEFORE INSERT ON TEST1
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL VALUES NEW.MODIFYTIME =
CURRENT_TIMESTAMP ;
CREATE TRIGGER TEST1_BUPD_TRG1 NO CASCADE BEFORE UPDATE OF STATUS ON TEST1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW MODE DB2SQL WHEN (
NEW.STATUS >= 0 AND OLD.STATUS <> 9 ) VALUES NEW.STATUS = 9 ,
NEW.MODIFYTIME = CURRENT_TIMESTAMP, NEW.ENDTIME = CURRENT_TIMESTAMP ;
BEFORE INSERT TRIGGER TEST:
INSERT INTO DMKASL.TEST1 (UNIQUE_ID) VALUES ('1');
ISSUE: Fails to set MODIFYTIME with CURRENT_TIMESTAMP, no errors
BEFORE UPDATE TRIGGER TEST:
UPDATE DMKASL.TEST1 SET STATUS=1 WHERE UNIQUE_ID='1';
ERROR: SQL Error [30000] [22005]: An attempt was made to get a data value
of type 'long' from a data value of type 'TIMESTAMP'.
The derby.log with derby.language.logStatementText=true enabled shows this:
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Begin compiling prepared statement:
UPDATE DMKASL.TEST1 SET STATUS=1 WHERE UNIQUE_ID='1' :End prepared
statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), End compiling prepared statement:
UPDATE DMKASL.TEST1 SET STATUS=1 WHERE UNIQUE_ID='1' :End prepared
statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Executing prepared statement: UPDATE
DMKASL.TEST1 SET STATUS=1 WHERE UNIQUE_ID='1' :End prepared statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Executing prepared statement: VALUES
CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS NUMERIC(10,0)) >= 0 AND CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(3)
AS NUMERIC(10,0)) <> 9 :End prepared statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Executing prepared statement: VALUES
CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS NUMERIC(10,0)) = 9 , CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS TIMESTAMP) = CURRENT_TIMESTAMP, CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(2)
AS TIMESTAMP) = CURRENT_TIMESTAMP :End prepared statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 230),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Committing
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 234),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Begin compiling prepared statement:
call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) :End prepared
statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 234),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), End compiling prepared statement:
call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) :End prepared
statement
Tue Nov 28 12:14:28 EST 2023 Thread[DRDAConnThread_4,5,main] (XID = 234),
(SESSIONID = 7), (DATABASE = data/test1), (DRDAID =
????????.????-723670463931461008{4}), Executing prepared statement: call
SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) :End prepared
statement with 16 parameters begin parameter #1: -20001 :end parameter
begin parameter #2: 20 :end parameter begin parameter #3:
longTIMESTAMP22005 :end parameter begin parameter #4: CSS10140 :end
parameter begin parameter #5: 0 :end parameter begin parameter #6: 0 :end
parameter begin parameter #7: 0 :end parameter begin parameter #8: 0 :end
parameter begin parameter #9: 0 :end parameter begin parameter #10: 0 :end
parameter begin parameter #11: :end parameter begin parameter #12: 22005
:end parameter begin parameter #13: NULL :end parameter begin parameter
#14: en_US :end parameter begin parameter #15: NULL :end parameter begin
parameter #16: null :end parameter logging
