"Forums @ Existanze" <forums@existanze.com> 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