Forums @ Existanze wrote:
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?
Hi Fotis,
I am not sure I understand what you are trying to do with the trigger.
Please bear with me because I may be garbling your question. It sounds
as though you may be trying to use triggers to do what a generated
column would do for you. That is, you want to use a trigger to fill a
column with the result of an expression computed out of other columns in
the row. I don't see how you can do this with an INSERT trigger. As
you've discovered, you can do this with an AFTER trigger.
Regards,
-Rick
Well, that is it for now, I hope someone answers or at least sheds
some light
Best Regards,
Fotis
