Refer to an other table's column in a trigger?

Question

Given:

CREATE TABLE worktags (
        worktag_id      integer         not null primary key,
        worktag         character(32)   not null default '' unique,

            ...
        last_updated    character(32)   not null default '[Error]'
);
CREATE TABLE truefacts (
        about           character(32)   not null primary key,
        fact            character(32)   not null
);

The following:

CREATE TRIGGER zz_worktags_last_updated AFTER UPDATE ON worktags BEGIN
  UPDATE worktags SET
    last_updated = truefacts.fact WHERE truefacts.about = 'Last Worktag Update';
END;

gives the error:

Error: near line 52: no such column: truefacts.fact

But the column exists, and the syntax diagram seems to indicate that

[[schema-name . ] table-name . ] column-name

is a legal expr for the right-side of a SET column-name = expr.

Answer 1

You would need to use a subquery to access the other (truefacts) table (as there is no FROM truefacts anywhere) e.g. :-

CREATE TRIGGER zz_worktags_last_updated AFTER UPDATE ON worktags BEGIN
  UPDATE worktags SET
    last_updated = (SELECT fact FROM truefacts WHERE about = 'Last Worktag Update');
END;

Saying that, there is then no need for the TRIGGER as the subquery could be embedded into the UPDATE.

e.g. consider the following example :-

DROP TRIGGER IF EXISTS zz_worktags_last_updated;
DROP TABLE IF EXISTS worktags;
DROP TABLE IF EXISTS truefacts;
CREATE TABLE worktags (
        worktag_id      integer         not null primary key,
        worktag         character(32)   not null default '' unique,
        last_updated    character(32)   not null default '[Error]'
);
CREATE TABLE truefacts (
        about           character(32)   not null primary key,
        fact            character(32)   not null
);

INSERT INTO truefacts VALUES('Last Worktag Update','xxx');
INSERT INTO worktags (worktag,last_updated) VALUES('mytag',(datetime('now')));
SELECT * FROM worktags;
UPDATE worktags SET last_updated = (SELECT fact FROM truefacts WHERE about = 'Last Worktag Update'), worktag = 'aaaa' WHERE worktag_id = 1;
SELECT * FROM worktags;

UPDATE truefacts SET fact = 'zzzz' WHERE rowid = 1;
CREATE TRIGGER zz_worktags_last_updated AFTER UPDATE ON worktags BEGIN
UPDATE worktags SET
    last_updated = (SELECT truefacts.fact FROM truefacts WHERE truefacts.about = 'Last Worktag Update');
END;

UPDATE worktags SET worktag = 'bbbb' WHERE worktag_id = 1;
SELECT * FROM worktags;

This :-

1. Drops the tables and triggers if they exist (so it can be rerun)
2. Crate the 2 tables and populates them.

3. Selects everything from the worktags table (just the 1 row)

4. Updates the row in the worktags table using a subquery (this is the no trigger required example)

5. Selects everything from the updated (without a trigger) worktags table.
6. Updates the fact column of the truefacts (to show that the trigger works) 6.Creates the trigger.
7. Updates the row in the worktags table, changing the worktag column, leaving the change to the last_updated column to be done by the trigger.
8. Selects everything from the updated by the trigger worktags table.

Running the above results in :-

and lastly