|Subject||Re: [firebird-support] Re: Auto increment|
>That depends. What do you want done when inserting a new row?
> Yes you are right, I made a mistake, I meant I think that a trigger is
> needed for this. The problem is I don't know how to write such a trigger.
> Can this be done with a trigger?
Set the <field2> value based on <field1>?
If so, you could try:
BEFORE INSERT trigger:
declare variable max_field2 integer;
select max(field2) from mytable where field1 = new.field1
if (max_field2 is null)
then max_field2 = 0; /* zero rows found */
new.field2 = max_field2 + 1;
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> --- In email@example.com, "Martijn Tonies"
> <m.tonies@u...> wrote:
> > Hello,
> > > I have the following situation: I have a table which contains data
> > > based on an auto incremented value. But instead of only 1 item for
> > > each increment a increment can have as many items as needed. Within an
> > > increment the first part of the key is the auto incremented number,
> > > the second part is also a number that needs to be auto incremented
> > > within the first key.
> > >
> > > So a part of the table could look like this:
> > >
> > > Part 1, Part 2
> > > 1 1
> > > 1 2
> > > 2 1
> > > 1 3
> > > 2 2
> > > 3 1
> > > 1 4
> > > 3 2
> > >
> > > What is the best way to solve this? I think I need a generator which
> > > gets the last number (part 2) there is for a given entry (part1) and
> > > increment that value.
> > You cannot use a generator like that. Generators generate numbers.
> > Period.
> > If you want some weird sequence, you need to maintain it yourself.
> > With regards,
> > Martijn Tonies
> > Database Workbench - developer tool for InterBase, Firebird, MySQL &
> MS SQL
> > Server.
> > Upscene Productions
> > http://www.upscene.com
> Yahoo! Groups Links