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] -----------------------------------------------------------------------------