On Thu, 2007-01-11 at 21:47 -0500, Dan Cooperstock 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?
In the context of an sqlite row trigger, "new" is a read-only *copy* of
the row just updated. You'll need to do something like:
UPDATE customer
SET customerid = (SELECT MAX(customerid) + 1 FROM customer)
WHERE oid = NEW.oid;
in the body of your trigger.
> 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
>
> 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]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
