Re: [sqlite] ON INSERT trigger

[Derrell . Lipman]

"Alex" <[EMAIL PROTECTED]> writes:

> CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
>
> INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
>
> INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
>
> In the given example the "UNIQUE" constraint will cause the row to be
> overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
> way to intercept this deletion. The ROWID will also be changed, so it cannot
> be used for tracking purposes. If anyone knows a solution, please, advice.

If I understand properly what you're trying to do, then you can probably add
an automatically-maintained map table, something like this:

  CREATE TABLE table (id TEXT PRIMARY KEY,
                      name TEXT,
                      surname TEXT,
                      secDbKey TEXT);

  CREATE TABLE map (id TEXT PRIMARY KEY,
                    secDbKey TEXT);

  CREATE TABLE SecDb (secDbKey TEXT PRIMARY KEY);

  CREATE TRIGGER table_insert_tr AFTER INSERT ON table
    FOR EACH ROW
    BEGIN
      DELETE FROM SecDb WHERE secDbKey =
        (SELECT secDbKey FROM map WHERE id = new.id);
      INSERT OR REPLACE INTO map VALUES (new.id, new.secDbKey);
    END

Note that the PRIMARY KEY definitions are necessary to ensure that the correct
rowid (which is also 'id') is overwritten on an INSERT OR REPLACE query.

Derrell