Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
On 12/3/23 11:34 AM, Steven Saunders wrote:
Hi Rick,
Can you confirm or not that Before Update and Before Insert triggers can or
cannot be used to modify column values on a table?
A trigger fires a triggeredStatement. The triggeredStatement can modify
columns in a table.
The triggeredStatement cannot modify a row which has not been INSERTed
yet. So I do not see how a BEFORE INSERT trigger can solve your problem.
Similarly, if the triggeredStatement UPDATEs a row in the triggering
table, then you will run the risk of infinite recursion--regardless of
whether the UPDATE trigger is a BEFORE or AFTER trigger.
The answer to this question will help me understand the limitations in
an attempt to migrate the software as-is codewise to work with Derby.
The only way to get it to work with Derby if still desired might be to move
the logic into the code vs. relying on the DB to keep the data as needed.
It was done the way it is because it was available in all the target DBMSs
and the DB handling the data in the correct state being centralized kept
data and behavior consistent over any modifications inside or outside the
scope of the software.
We are also looking at HyperSQL to fill this need for a lighter weight,
transportable file based DBMS. So far it appears to work with Before
Update and Insert they way needed and as the other working DBMS options
(again Oracle DB, SQL Server, DB2 LUW and DB2 zOS). MySQL is another DBMS
that might be evaluated but not for this particular need.
Moving to using Procedures for rights seems off the path of feasibly
solving the need to intercepting DML statements to control column values
the application needs.
Thanks for trying to help, it is very much appreciated,
Steve
On Fri, Dec 1, 2023 at 1:42 PM Rick Hillegas
wrote:
Is there some reason that you have to solve this problem with triggers?
An alternative solution would be to perform your integrity checks in a
database procedure which runs with DEFINERS rights and to restrict
UPDATE privilege on the table to the table owner.
On 12/1/23 10:15 AM, Steven Saunders wrote:
Hi Rick,
I guess the first question of most importance is:
Can we use Before Update or Before Insert triggers to intercept and
modify column values (timestamps or otherwise)?
The over simplified example was to show that the before triggers were not
functioning as expected so attempts as solutions are not
necessarily solving the real schema issues found.
I have now done some experimenting with HypberSQL DB and it appears to
handle the Before triggers we need well so far. We may have to use that
instead if we can't use Before triggers the way we need in DerbyDB.
Thanks for your help,
Steve
On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas
wrote:
You could replace the INSERT trigger with a generated column. I don't
see
how to eliminate the UPDATE trigger and preserve the behavior you want.
Here's how to eliminate the INSERT trigger. First make the following
class
visible on the JVM's classpath:
import java.sql.Timestamp;
public class TimeFunctions
{
public static Timestamp currentTimestamp() { return new
Timestamp(System.currentTimeMillis()); }
}
Then declare the following user-defined function. Note that you have to
lie and say that the function is deterministic:
CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP
LANGUAGE JAVA
DETERMINISTIC
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'TimeFunctions.currentTimestamp';
Then declare your table with a generated column. No INSERT trigger
should
be needed:
CREATE TABLE TEST1
(
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1,
INCREMENT BY 1 ) PRIMARY KEY,
UNIQUE_ID VARCHAR (47) NOT NULL ,
CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),
MODIFYTIME TIMESTAMP,
ENDTIME TIMESTAMP ,
STATUS NUMERIC (10) WITH DEFAULT 0
);
On 11/29/23 7:44 AM, Steven Saunders wrote:
Hi Rick,
Thanks for the alternative, it looks like you switched from Before
Insert
and Before Update to After Insert and After Update, respectfully.
That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying
to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.
Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column
values
such as adding timestamps or other data type values? That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.
Thanks very much,
-Steve
On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
wrote:
Hi Steven,
Derby hews fairly closely to
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
Hi Rick,
Can you confirm or not that Before Update and Before Insert triggers can or
cannot be used to modify column values on a table?
The answer to this question will help me understand the limitations in
an attempt to migrate the software as-is codewise to work with Derby.
The only way to get it to work with Derby if still desired might be to move
the logic into the code vs. relying on the DB to keep the data as needed.
It was done the way it is because it was available in all the target DBMSs
and the DB handling the data in the correct state being centralized kept
data and behavior consistent over any modifications inside or outside the
scope of the software.
We are also looking at HyperSQL to fill this need for a lighter weight,
transportable file based DBMS. So far it appears to work with Before
Update and Insert they way needed and as the other working DBMS options
(again Oracle DB, SQL Server, DB2 LUW and DB2 zOS). MySQL is another DBMS
that might be evaluated but not for this particular need.
Moving to using Procedures for rights seems off the path of feasibly
solving the need to intercepting DML statements to control column values
the application needs.
Thanks for trying to help, it is very much appreciated,
Steve
On Fri, Dec 1, 2023 at 1:42 PM Rick Hillegas
wrote:
> Is there some reason that you have to solve this problem with triggers?
>
> An alternative solution would be to perform your integrity checks in a
> database procedure which runs with DEFINERS rights and to restrict
> UPDATE privilege on the table to the table owner.
>
> On 12/1/23 10:15 AM, Steven Saunders wrote:
> > Hi Rick,
> >
> > I guess the first question of most importance is:
> > Can we use Before Update or Before Insert triggers to intercept and
> > modify column values (timestamps or otherwise)?
> >
> > The over simplified example was to show that the before triggers were not
> > functioning as expected so attempts as solutions are not
> > necessarily solving the real schema issues found.
> >
> > I have now done some experimenting with HypberSQL DB and it appears to
> > handle the Before triggers we need well so far. We may have to use that
> > instead if we can't use Before triggers the way we need in DerbyDB.
> >
> > Thanks for your help,
> > Steve
> >
> >
> >
> > On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas
> > wrote:
> >
> >> You could replace the INSERT trigger with a generated column. I don't
> see
> >> how to eliminate the UPDATE trigger and preserve the behavior you want.
> >>
> >> Here's how to eliminate the INSERT trigger. First make the following
> class
> >> visible on the JVM's classpath:
> >>
> >> import java.sql.Timestamp;
> >>
> >> public class TimeFunctions
> >>
> >> {
> >>
> >> public static Timestamp currentTimestamp() { return new
> Timestamp(System.currentTimeMillis()); }
> >>
> >> }
> >>
> >>
> >> Then declare the following user-defined function. Note that you have to
> >> lie and say that the function is deterministic:
> >>
> >> CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP
> >>
> >> LANGUAGE JAVA
> >>
> >> DETERMINISTIC
> >>
> >> PARAMETER STYLE JAVA
> >>
> >> NO SQL
> >>
> >> EXTERNAL NAME 'TimeFunctions.currentTimestamp';
> >>
> >>
> >> Then declare your table with a generated column. No INSERT trigger
> should
> >> be needed:
> >>
> >> CREATE TABLE TEST1
> >>
> >> (
> >>
> >>ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1,
> INCREMENT BY 1 ) PRIMARY KEY,
> >>
> >>UNIQUE_ID VARCHAR (47) NOT NULL ,
> >>
> >>CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),
> >>
> >>MODIFYTIME TIMESTAMP,
> >>
> >>ENDTIME TIMESTAMP ,
> >>
> >>STATUS NUMERIC (10) WITH DEFAULT 0
> >>
> >> );
> >>
> >>
> >>
> >> On 11/29/23 7:44 AM, Steven Saunders wrote:
> >>
> >> Hi Rick,
> >>
> >> Thanks for the alternative, it looks like you switched from Before
> Insert
> >> and Before Update to After Insert and After Update, respectfully.
> >>
> >> That will add multiple updates for one Insert or Update inturn causing
> >> unwanted triggers to fire in a slightly more complex schema I am trying
> to
> >> port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
> >> actually tried this switch on the target schema which caused unwanted
> >> trigger firing and worse case scenario an exception for trigger depth.
> >>
> >> Is it true then that we are not able to use any Before Update or Before
> >> Insert triggers to intercept Inserts and Updates and affect column
> values
> >> such as adding timestamps or other data type values? That would be
> >> unfortunate if it were the case as there would be no opportunity to
> >> manipulate the data before it is put in a table unless the only way is
> >> calling via java classes in the Before triggers.
> >>
> >> Thanks very much,
> >> -Steve
> >>
> >> On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
>
> >> wrote:
> >>
> >>
> >> Hi Steven,
> >>
> >> Derby hews fairly closely to SQL Standard
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
Is there some reason that you have to solve this problem with triggers?
An alternative solution would be to perform your integrity checks in a
database procedure which runs with DEFINERS rights and to restrict
UPDATE privilege on the table to the table owner.
On 12/1/23 10:15 AM, Steven Saunders wrote:
Hi Rick,
I guess the first question of most importance is:
Can we use Before Update or Before Insert triggers to intercept and
modify column values (timestamps or otherwise)?
The over simplified example was to show that the before triggers were not
functioning as expected so attempts as solutions are not
necessarily solving the real schema issues found.
I have now done some experimenting with HypberSQL DB and it appears to
handle the Before triggers we need well so far. We may have to use that
instead if we can't use Before triggers the way we need in DerbyDB.
Thanks for your help,
Steve
On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas
wrote:
You could replace the INSERT trigger with a generated column. I don't see
how to eliminate the UPDATE trigger and preserve the behavior you want.
Here's how to eliminate the INSERT trigger. First make the following class
visible on the JVM's classpath:
import java.sql.Timestamp;
public class TimeFunctions
{
public static Timestamp currentTimestamp() { return new
Timestamp(System.currentTimeMillis()); }
}
Then declare the following user-defined function. Note that you have to
lie and say that the function is deterministic:
CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP
LANGUAGE JAVA
DETERMINISTIC
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'TimeFunctions.currentTimestamp';
Then declare your table with a generated column. No INSERT trigger should
be needed:
CREATE TABLE TEST1
(
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT
BY 1 ) PRIMARY KEY,
UNIQUE_ID VARCHAR (47) NOT NULL ,
CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),
MODIFYTIME TIMESTAMP,
ENDTIME TIMESTAMP ,
STATUS NUMERIC (10) WITH DEFAULT 0
);
On 11/29/23 7:44 AM, Steven Saunders wrote:
Hi Rick,
Thanks for the alternative, it looks like you switched from Before Insert
and Before Update to After Insert and After Update, respectfully.
That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.
Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column values
such as adding timestamps or other data type values? That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.
Thanks very much,
-Steve
On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
wrote:
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
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
Hi Rick,
I guess the first question of most importance is:
Can we use Before Update or Before Insert triggers to intercept and
modify column values (timestamps or otherwise)?
The over simplified example was to show that the before triggers were not
functioning as expected so attempts as solutions are not
necessarily solving the real schema issues found.
I have now done some experimenting with HypberSQL DB and it appears to
handle the Before triggers we need well so far. We may have to use that
instead if we can't use Before triggers the way we need in DerbyDB.
Thanks for your help,
Steve
On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas
wrote:
> You could replace the INSERT trigger with a generated column. I don't see
> how to eliminate the UPDATE trigger and preserve the behavior you want.
>
> Here's how to eliminate the INSERT trigger. First make the following class
> visible on the JVM's classpath:
>
> import java.sql.Timestamp;
>
> public class TimeFunctions
>
> {
>
> public static Timestamp currentTimestamp() { return new
> Timestamp(System.currentTimeMillis()); }
>
> }
>
>
> Then declare the following user-defined function. Note that you have to
> lie and say that the function is deterministic:
>
> CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP
>
> LANGUAGE JAVA
>
> DETERMINISTIC
>
> PARAMETER STYLE JAVA
>
> NO SQL
>
> EXTERNAL NAME 'TimeFunctions.currentTimestamp';
>
>
> Then declare your table with a generated column. No INSERT trigger should
> be needed:
>
> CREATE TABLE TEST1
>
> (
>
> ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT
> BY 1 ) PRIMARY KEY,
>
> UNIQUE_ID VARCHAR (47) NOT NULL ,
>
> CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),
>
> MODIFYTIME TIMESTAMP,
>
> ENDTIME TIMESTAMP ,
>
> STATUS NUMERIC (10) WITH DEFAULT 0
>
> );
>
>
>
> On 11/29/23 7:44 AM, Steven Saunders wrote:
>
> Hi Rick,
>
> Thanks for the alternative, it looks like you switched from Before Insert
> and Before Update to After Insert and After Update, respectfully.
>
> That will add multiple updates for one Insert or Update inturn causing
> unwanted triggers to fire in a slightly more complex schema I am trying to
> port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
> actually tried this switch on the target schema which caused unwanted
> trigger firing and worse case scenario an exception for trigger depth.
>
> Is it true then that we are not able to use any Before Update or Before
> Insert triggers to intercept Inserts and Updates and affect column values
> such as adding timestamps or other data type values? That would be
> unfortunate if it were the case as there would be no opportunity to
> manipulate the data before it is put in a table unless the only way is
> calling via java classes in the Before triggers.
>
> Thanks very much,
> -Steve
>
> On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
>
> wrote:
>
>
> 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
>
>
>
>
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
You could replace the INSERT trigger with a generated column. I don't
see how to eliminate the UPDATE trigger and preserve the behavior you want.
Here's how to eliminate the INSERT trigger. First make the following
class visible on the JVM's classpath:
import java.sql.Timestamp;
public class TimeFunctions
{
public static Timestamp currentTimestamp() { return new
Timestamp(System.currentTimeMillis()); }
}
Then declare the following user-defined function. Note that you have to
lie and say that the function is deterministic:
CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP
LANGUAGE JAVA
DETERMINISTIC
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'TimeFunctions.currentTimestamp';
Then declare your table with a generated column. No INSERT trigger
should be needed:
CREATE TABLE TEST1
(
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT
BY 1 ) PRIMARY KEY,
UNIQUE_ID VARCHAR (47) NOT NULL ,
CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),
MODIFYTIME TIMESTAMP,
ENDTIME TIMESTAMP ,
STATUS NUMERIC (10) WITH DEFAULT 0
);
On 11/29/23 7:44 AM, Steven Saunders wrote:
Hi Rick,
Thanks for the alternative, it looks like you switched from Before Insert
and Before Update to After Insert and After Update, respectfully.
That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.
Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column values
such as adding timestamps or other data type values? That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.
Thanks very much,
-Steve
On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
wrote:
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
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
Hi Rick,
Thanks for the alternative, it looks like you switched from Before Insert
and Before Update to After Insert and After Update, respectfully.
That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby. I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.
Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column values
such as adding timestamps or other data type values? That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.
Thanks very much,
-Steve
On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
wrote:
> 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
>
Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime
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
