Rick Hillegas wrote:
Forums @ Existanze wrote:
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()
)
Hi Fotis,
Hello rick, thanks for taking the time to answer,
Today in Derby, the DEFAULT clause can only specify a constant value.
This is described in the Derby Reference Guide
(http://db.apache.org/derby/docs/10.3/ref/) in a section titled
"column-definition".
What you want to do could be accomplished with a SQL feature called
Generated Columns, briefly described by DERBY-481. I am hoping this
feature will get some attention soon--but, of course, that does not
help you today.
Here's a workaround which other people have found useful:
1) Call custom_function() in your application code everywhere that you
insert or update the affected column.
2) Add a CHECK constraint to the column to sanity-check that you
haven't missed any places in your code where the column is touched.
Like so:
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 CHECK ( test_entry_code =
custom_function() )
)
Hope this helps,
-Rick
This is way too much work, and I will probably need to find some other
solution, but thank you for you suggestion. Any word on the trigger side?
Well, that is it for now, I hope someone answers or at least sheds
some light
Best Regards,
Fotis
