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