Besides the trigger issue with sqlite. I think you have a design issue with
your tables.
You are using a composite key. Why not have a master table of customer I'ds
that you maintain, whith only the customer_id as the PK and autoincrement.
The the table you refer to would then be a child of the customer, that would
contain the year...
That should eliminate the need for the trigger.
Dan Cooperstock <[EMAIL PROTECTED]> wrote: I have a slightly unusual situation
in my database design. Several of my
tables have primary keys composed of two integer fields, such as customerid
and year. The customerid should be an Autoincrement field, in the sense that
when a new customer is added, it should get an auto-generated customerid
from the database (different from all others).
I can't declare the customerid as Primary Key Autoincrement, because it's
not unique - it's only unique when I put it together with the year. (The
reason for that is that I copy all customer records to new records at each
year end, with the same customerid but a new year. Yes, there _is_ a good
reason for that.)
What I have come up with at this point is declaring the pair of fields to be
the Primary Key, and adding a trigger such as:
create trigger insert_customer before insert on customer when NEW.customerid
is null begin
update NEW set customerid = (select max(customerid) + 1 from customer);
end
This doesn't quite work, though, because I have declared customerid as Not
Null, and that stops any insert that doesn't set the customerid, even though
the trigger would fix it! I guess the Not Null constraint "fires" before the
trigger does.
Even with customerid allowing nulls, though, this trigger doesn't work. When
I do an insert, I get an error message "No such table: main.NEW". Does that
mean we can't update the NEW "table" in a before insert or update trigger?
If not, how can I get this sort of effect?
Is there any smart way to make this work, hopefully still using
Autoincrement, or at least to make the trigger work? I'd also really rather
not set customerif to allow nulls, since that would then apply to updates
too.
Thank you.
---------------------------
Dan Cooperstock
DONATION web site: http://www.FreeDonationSoftware.org
DONATION Support forums: http://forums.FreeDonationSoftware.org
E-mail: mailto:[EMAIL PROTECTED]
Home Phone: 416-423-9064
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
