SQLite Forum

adding record if it doesn't already exist
Login
Well, that is a design flaw (failure to store needed information) and has nothing whatsoever to do with the question asked.  Unless the inserted record contains a timestamp of when it was inserted, there is no way to tell when the record was inserted.  Playing with pseudokeys will not be helpful in this regard.  It also does not really matter what some excrement-head thinks "after the fact" -- they can either accept the answer "I don't know" (which by the way is an incorrect answer -- the correct answer would be "that information was not recorded").

And going back to the post to which you replied, it only appears to the uneducated that there is a need to know the pseudokey for the row.  It is actually not needed for anything at all.

```
create table names
(
 name_id integer primary key,
 name text not null collate nocase unique
);
create table houses
(
 house_id integer primary key,
 address text not null collate nocase unique
);
create table peasants
(
 id integer primary key,
 name_id integer references names,
 house_id integer references houses
);
create view persons 
as select peasants.id, 
          name, 
          address 
     from peasants, names, houses
    where (peasants.name_id is null or peasants.name_id == names.name_id)
      and (peasants.house_id is null or peasants.house_id == houses.house_id);
create trigger ins_person instead of insert into persons
begin
  select raise(ABORT, 'Name cannot be NULL') where new.name is null;
  insert or ignore into names (name) values (new.name);
  insert or ignore into houses (address) select new.address where new.address is not null;
  insert into peasants (name_id, house_id)
  select (select name_id from names where name == new.name),
         (select house_id from houses where address == new.address);
end;
```

Seems to me that there is no need to EVER deal with the pseudokeys.  So I can see why someone might think they need to know the pseudokey, but they are incorrect.