"Forums @ Existanze" <[EMAIL PROTECTED]> writes:
> 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
I sounds like auto-generated columns is what you need. Something like
this should do the trick:
CREATE TABLE person (
person_code INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
...
)
See http://db.apache.org/derby/docs/10.3/ref/rrefsqlj37836.html for
details.
> 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.
One way to do this is to factor out the "code" from the person table and
store it in a separate table.
CREATE TABLE codes (
person_code INT UNIQUE NOT NULL REFERENCES person,
code_value VARCHAR(10) UNIQUE NOT NULL)
Then the trigger can insert a row into that table instead of modifying
the row that's being inserted into the person table.
--
Knut Anders
