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 <[email protected]>
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 <[email protected]>
> <[email protected]>
> 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
>
>
>
>
