I have a question about how to reset values if an insert fails. Using
the following tables, is there any relatively straightforward way to
set LastNameIDInserted to -1 if the UNIQUE constraint on tblNames
fails?
CREATE TABLE tblNames
(
NameID INTEGER PRIMARY KEY,
First CHAR(10),
Last CHAR(10),
CONSTRAINT uniquepairs UNIQUE
(
First,
Last
)
);
CREATE TABLE tblNamesLastInserted
(
LastNameIDInserted INTEGER
) ;
CREATE TRIGGER updateLastInsertedID AFTER INSERT ON tblNames
BEGIN
UPDATE tblNamesLastInserted
SET LastNameIDInserted = new.NameID ;
END ;
INSERT INTO tblNames (First, Last) VALUES ('Keith', 'Herold') ;
-- LastNameIDInserted = 1
INSERT INTO tblNames (First, Last) VALUES('Keith', 'Herold');
-- LastNameIDInserted = -1
I know about last_insert_rowid, but that doesn't tell you whether an
insert succeeded or not, just what the last rowid was. I tried
modifying the trigger to use a case statement that set
LastNameIDInserted = -1 if change_count() was 0, but that didn't seem
to do anything (and I suppose it's because the trigger never fires,
because the insert fails, and there is no 'AFTER' in this case?).
I suppose a 'BEFORE' trigger could do this for me, but I was
wondering if there was some other way?
I am trying to move some work currently done in C++ into the database,
so that I can rely on the database to do the heavy-lifting.
-- Keith
******************************************************
- Ever notice how 'big' isn't, compared to 'small'?
- I'm not a professional; I just get paid to do this.
- Rules for programming:
1. Get it working, right?
2. Get it working right.
- Things I've learned about multithreaded programming:
123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt
rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt
iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
******************************************************
