Hello again,
I am going to top post on this one.
To be totally honest the trigger question came about for two reasons.
The first is that we used a single sequence to
provide primary integers to three different tables in postgresql.
As far as I know this is not possible in derby. So I though of creating
a before insert trigger which will modify the value of
the entry column before being inserted. The value could be generated a
function which just increments a number on some
table each time, like a pseudo sequence. This trigger is then added all
three tables.
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
The second reason is that we have a specific way of creating a "code"
which gets attached to each person entry in the
table. For example, the code can be , the COUNT(*) + the month+ the
year+ the COUNT in month, which will give a 10
digit number we use. In postgres we have a before insert trigger that
creates this number, sets the value of the column
and the row gets inserted.
I am currently looking at hsqldb to see if I can overcome these
postgresql only obstacles.
Thank you very much though, for all your help
Fotis
Rick Hillegas wrote:
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
