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 <rick.hille...@gmail.com>
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
>
