Hello all,
As the subject reads, my development team and I are trying to migrate to
apache derby, because
we will start deploying our application with the database embedded,
instead of having the hassle
of installing a database on each client. Following are some of my
question regarding this migration,
that I haven't been able to find an answer to. I hope I don't tire you
with this long email.
After reading the documentation and viewing various examples I have been
able to successfully
migrate my "schema" to derby, I have been able to recreate all of our
tables on a derby database.
Because of previous (now very apparent) incorrect decisions in relying
on some Postgresql specific
functions, I have found myself at a dead end in completing this
migration process.
In order to make up for some of the functionality I want to create
several functions, stored procedures and triggers that will do the job.
Forgive me if I compare postgres and derby too much, but that is the way
that I know how to explain it.
When I created a "BEFORE INSERT" trigger in postgres I was able to
modify any of the fields of the entry that was about to be added. In
derby I haven't found any way to do this.
Using the following example:
CREATE TRIGGER set_proper_id
NO CASCADE BEFORE INSERT ON person
REFERENCING NEW AS new_person
FOR EACH ROW MODE DB2SQL
new_person.person_code = --some generated code
I am under the assumption that by referencing the new entry I can
modified it before the constraint checks,
at least that is what is said in the reference. Am I missing something
here? Is what I am trying to do just not possible?
I understood most of the example which used AFTER UPDATE or AFTER
INSERT, but in those situations my constraints come into play on the
person table before anything gets inserted and everything breaks.
Is the trigger function supposed to return a value? In postgresql it was
clearly defined that we should return the entry that was going to be
inserted. It was very convenient that I didn't have to create the whole
record just to change a single field and return the modified entry, I
hope that is not the case here because the person table has too many fields.
When I create a table, is it possible to specify the default value of a
column to be the result of a user_function?
For example
CREATE TABLE test(
test_id integer not null generated always as identity(start with 1,
increment by 1) primary key,
test_entry_code VARCHAR(10) NOT NULL DEFAULT custom_function()
)
Well, that is it for now, I hope someone answers or at least sheds some
light
Best Regards,
Fotis
