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