Below is a minimal case of SQL code that's not working. I minimized it to help the experts answer my question, then expanded it with comments to provide an interesting use case. I don't only want to ask a question: I want to give something back.

Either I am having a very PEBKAC day, or I found a rare bug. I know which is more likely.

Tested with the shell on SQLite 3.27.2 2019-02-25 16:06:06 bd49a8271d650fa89e446b42e513b595a717b9212c91dd384aab871fc1d0alt1, because that's what my OS package manager is providing. I intend to compile and try a current version Soon(TM), but I already spent too much time banging my head against the SQL. I tried dozens of minor variations.

Thanks for any assistance.

/*
 * Preface:  This tri-valued makeshift semaphore is partly based on
 * knowing that SQLite uses very little storage for the values NULL,
 * 0, and 1.  Thus the avoidance of using 0, 1, and 2.
 *
 * Optimize, optimize!
 */

/*
 * Part 0: Table set-up.
 */
CREATE TABLE "data" (
	"item" INTEGER PRIMARY KEY,
	"semaphore" INTEGER DEFAULT NULL,
	"data" BLOB
);

CREATE INDEX "sem_idx" ON "data" ("semaphore");

/*
 *
 * The following must be allowed:
 *
 *   -- Raise semaphore from NULL to 0.
 *   -- Raise sempahore from 0 to 1.
 *   -- Lower sempahore from 0 to NULL.
 *
 * The following must be forbidden:
 *
 *   -- Set semaphore from 0 to 0.
 *
 * All other cases are irrelevant --- not possible in the application code.
 */
CREATE TRIGGER "check_semaphore" BEFORE UPDATE OF "semaphore" ON "data"
	FOR EACH ROW
		WHEN "OLD.semaphore" IS NOT NULL AND "NEW.semaphore" = 0
	BEGIN
		SELECT RAISE(ROLLBACK, 'Race condition.');
	END;

/*
 * Part 1: Initial data.
 */
INSERT INTO "data" ("item") VALUES (0);

/*
 * Part 2: Using the table.
 */

BEGIN TRANSACTION;
/*
 * Here, the application does a complex SELECT that grabs the
 * INTEGER PRIMARY KEYs and the data of a subset of items
 * that have a NULL semaphore.
 *
 * -- Huge compound SELECT!
 *
 * Now, before it works on these items, it must tell other threads
 * to leave them alone.  To assure atomicity of each thread's view,
 * it does this after the SELECT within an explicit transaction.  It
 * needs to perform the SELECT first, so that it can know the INTEGER
 * PRIMARY KEYs to update.
 */

UPDATE "data" SET "semaphore" = 0 WHERE "item" = 0;
COMMIT;

/*
 * Performing the same update again *should* fire the trigger, raising
 * a rollback.  However, it does not:
 */
-- UPDATE "data" SET "semaphore" = 0 WHERE "item" = 0;

/*
 * When an item is successfully processed, the data will be written; and
 * the value of the semaphore will be raised to 1.  If processing fails,
 * the semaphore will be set back to NULL so that the item can be retried
 * in the next batch --- perhaps by another thread.
 *
 * On success:
 * UPDATE "data" SET "semaphore" = 1, "data" = ... WHERE "item" = 0;
 */

b44011d7ffde847a6f074de029c33dd4ea65514417b6c79eae04670a8b9fbc83

Nice catch. Quoting them together would make the column name itself contain the OLD., whereas you want that outside the quotes because it is working as a special table name. (Same also goes for NEW., of course.)

Yes, it's not actually a table name but rather a special keyword, but it sits in a similar syntactic position to a table name.

just replace "OLD.semaphore" with OLD.semaphore , or "OLD"."semaphore"

Sharp eyes! Thanks. Problem solved.

I (anonymous OP) took on the habit of quoting identifiers, as a matter of code hygiene --- and because SQLite allows such neat tricks as using the null string as an identifier. Well, here, I spent hours staring at this simple SQL statement without noticing that I misquoted. I do technically know whether the prefixes are parts of the identifier, or identifiers themselves. Whoops!

Either I am having a very PEBKAC day,

Although this was my mistake, it is also a doc bug. I did go back and RTFM before making a forum thread. As of faa8501a4aa1f31e, the documentation of CREATE TRIGGER uses quotes in an ambiguous, misleading way:

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", [...]

It seems that filing a doc bug is not allowed with anonymous login? (I thank the SQLite developers for making the forum, the documentation, and most other functionality all available with no Javascript, no identifying information, no installation of 27,000 Node.js recursive package dependencies after four hours of fighting with Yarn bugs, and no other chic modern "web app" stuff.)

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", [...]

Those are what is known as "air-quotes", not identifier quotes.

The referent of the term "quoting hell" is a real thing. As your post shows.

Personally I think all inline code-fragments appearing in the documentation should be clearly rendered via both typography (i.e. a fixed-size font), and further highlighted by some kind of box around it, by having a different background (with rounded edges), similar to how back-tick-code is often rendered in Markdown (but not in Fossil, sadly). That way, no need to resort to English air-quotes as Keith calls them, to introduce such code, and thus avoid the ambiguity pointed out by the OP, when the code fragment itself can or could be containing quotes. Whether such fragments use Markdown-back-ticks or some other markup to introduce them, in the doc's source code, that's for someone else to decide of course.

E.g. in the post I wrote today, I used many %x fragments, where the fixed-font is not obvious to notice, given how small the fragments are. Having a different background, the way MS Teams does it for example, is much more visible and obvious.

I wish both the Fossil forums and SQLite doc would adopt this practice. FWIW.

And further highlighted by some kind of box around it, by having a different background (with rounded edges), similar to how back-tick-code is often rendered in Markdown (but not in Fossil, sadly)

That marking is 100% skin-dependent in fossil. Some skins do it, some don't. Try switching skins (via the Skins entry in the "hamburger" menu) and maybe you'll find one more to your liking.

My first thought was there may be an optimization where setting X to X does not cause any writes or triggers to fire, but some quick testing proved that not to be the case.

I then teased out your script from the posted blog, and tried with that, which also worked perfectly (i.e. aborted when it should have).

DROP TABLE IF EXISTS data;

CREATE TABLE data(
  item INTEGER PRIMARY KEY,
  semaphore INTEGER DEFAULT NULL,
  data BLOB
);
CREATE INDEX sem_idx ON data (semaphore);

CREATE TRIGGER check_semaphore
    BEFORE UPDATE OF semaphore ON data
    FOR EACH ROW WHEN OLD.semaphore IS NOT NULL AND NEW.semaphore = 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Race condition.');
END;

INSERT INTO data (item) VALUES (0);

BEGIN TRANSACTION;
UPDATE data SET semaphore = 0 WHERE item = 0;

UPDATE data SET semaphore = 0 WHERE item = 0;
-- Script ends here...

UPDATE data SET semaphore=1, data = '0x0123' WHERE item = 0;
COMMIT;

Script stops where indicated, the log showing:

[2022-04-10 10:46:56.742] ERROR (1811) : abort at 7 in [UPDATE data SET semaphore = 0 WHERE item = 0;]: Race condition.
[2022-04-10 10:46:56.742] QUERY        : Failed Query: UPDATE data SET semaphore = 0 WHERE item = 0;
[2022-04-10 10:46:56.742] INTERFACE    : DB Error: Race condition.

So no, I do not know why your SQL doesn't work, but if what you have shown is a faithful representation of your actual SQL, then it very much should work.

The other loose end is that I am using 3.37.2, so version 3.27.2 may have a deficit of sorts, but I do not recall such a fix being listed (granted, my recollection is not flawless).

Can you run that script above through your SQLite CLI on your system (which I imagine would be 3.27.2 since you mention it comes bundled) and see if it fails as required or not?