I'm hitting some unexpected behavior where an `AFTER UPDATE` trigger's
`WHEN` clause is being evaluated by an upsert, even though there is no
conflict (and hence, the trigger shouldn't be evaluated).
As an example, let's say we want to ensure that an item's name is always
uppercase.
>~~~
CREATE TABLE Items (name TEXT PRIMARY KEY);
~~~
>~~~
CREATE TRIGGER uppercase_name
AFTER UPDATE ON Items
WHEN lower(name) != upper(name)
BEGIN
UPDATE Items SET name = upper(name);
END;
~~~
_(`AFTER INSERT` trigger omitted for brevity)_
Now we upsert an item:
>~~~
INSERT INTO Items VALUES ('Apple')
ON CONFLICT (name) DO UPDATE SET
name = excluded.name;
~~~
This will produce `Error: no such column: name`. It seems like the trigger shouldn't be evaluated at all since there is no conflict, and thus there's no `UPDATE` happening. Is this expected behavior or a bug?
Notes:
- I tried a couple of things, curious if they would have any effect, but
they didn't:
- Inserting a row before the upsert
- Adding an `AFTER INSERT` trigger before the `AFTER UPDATE`
- I tested on versions `3.34.1` and `3.35.4`.
- Code to test/replicate is here: [https://pastebin.com/raw/9YuAEywU](https://pastebin.com/raw/9YuAEywU)
Thanks!