I'm fairly new to SQL, so please bear with me.
I'm writing an application in which I represent an item in a table,
and I would also like a history table in which I represent the item as
it exists on various dates.
The item's primary key is an integer. I have represented the history
table with a compound key of the item's primary key and a column
indicating the state of that item. Eg.
create table item (item_id integer primary key,state varchar(8)
);
create table item_hist (item_id integer primary key,state varchar(8),date date,
primary key (item_id, state)
);
It happens that I have to query another application for the status of
these items, and it is vastly more efficient to get the status of all
the items than it is to query for each individually.
What I would like to do (and have manage to kludge together in Perl
DBI) is to attempt to insert (item_id, state, today's date) into
item_hist, and in the cases where that item/state combo exists
already, the insert will fail. However, if I am using {AutoCommit =>
0}, the whole transaction bombs, so I can only get away with this if
I'm AutoCommit'ing.
Any thoughts on how I can do this with AutoCommit => 0? I would
rather not have to look up all the status rows to figure out if an
error will occur.
--Dave
