sqlite - How to get INSERT OR IGNORE to work

I'm trying to insert data into a table. I would like to insert the row if the column doesn't have the data already - regardless of the other columns.

CREATE TABLE t (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR,
    other       INT
);
INSERT OR IGNORE INTO t (name) VALUES ('a');
INSERT OR IGNORE INTO t (name) VALUES ('a');
INSERT OR IGNORE INTO t (name) VALUES ('a');

With the above snippet I end up with 3 rows, not 1 as I would have thought. If it matters the actual sql is happening inside of a INSTEAD OF INSERT trigger, this is just a simple test case.

Replace

CREATE TABLE t (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR,
    other       INT
);

with

CREATE TABLE t (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR UNIQUE,
    other       INT
);

Then you will get

sqlite> CREATE TABLE t (
   ...>     id          INTEGER PRIMARY KEY,
   ...>     name        VARCHAR UNIQUE,
   ...>     other       INT
   ...> );
sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a');
sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a');
sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a');
sqlite> select * from t ;
1|a|

That would only work for the primary key field or unique constraints:

> The optional conflict-clause allows the specification of an > alternative constraint conflict resolution algorithm to use during > this one INSERT command.

Further:

> The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints > (and to PRIMARY KEY constraints which for the purposes of this section > are the same thing as UNIQUE constraints). The ON CONFLICT algorithm > does not apply to FOREIGN KEY constraints. There are five conflict > resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and > REPLACE. The default conflict resolution algorithm is ABORT.