I have the following table:
create table if not exists balance (
id integer PRIMARY KEY AUTOINCREMENT,
exchange integer,
FOREIGN KEY (exchange) REFERENCES exchanges(id)
);
where the field exchange references a "dictionary" table, defined as this:
create table if not exists exchanges (
id integer PRIMARY KEY AUTOINCREMENT,
exchangeName text
);
Now, I would like the dictionary table echanges work as transparently as possible - i.e. I would like to just run one insert command into table balance, just giving it the string value exchangeName. I want it to automatically check the exchanges if there is already corresponding record, if yes, then put its id into the new record in balance table; if no, then insert a new record to the exchanges dictionary. I guess the means to do it in SQLite are triggers? Can this be done using SQLite triggers?
