How to force a SQLite trigger to modify an existing row

Question

I have an SQLite table with a type column STRINGnamed "NOTES". I want to create a trigger that automatically updates the contents of a NOTES column, but does not replace it completely.

The following steps do not work, I see no errors, the NOTES column is never updated.

CREATE TRIGGER AlterNote 
    AFTER INSERT ON MyTable 
    FOR EACH ROW 
    BEGIN UPDATE MyTable 
        SET NOTES= NOTES || 'DATEMODIFIED: ' || date('now')
        WHERE rowid=NEW.rowid; 
    END;

This works, apparently, because I no longer refer to what I am updating:

CREATE TRIGGER AlterNote 
    AFTER INSERT ON MyTable 
    FOR EACH ROW 
    BEGIN UPDATE MyTable 
        SET NOTES= 'DATEMODIFIED: ' || date('now')
        WHERE rowid=NEW.rowid; 
    END;

Is there any way to do this? Basically NOTES = NOTES + "blah"

Answer 1

Use the syntax new.COLUMN_NAME.

CREATE TRIGGER AlterNote 
    AFTER INSERT ON MyTable 
    FOR EACH ROW 
    BEGIN UPDATE MyTable 
        SET NOTES= new.Notes || ',DATEMODIFIED: ' || date('now')
        WHERE rowid=NEW.rowid; 
    END;

See an example implementation.

sqlite>
sqlite> create table mytable(Notes text);
sqlite> insert into mytable(Notes) Values('aa');
sqlite> select * from mytable;
aa
sqlite> CREATE TRIGGER AlterNote
   ...>     AFTER INSERT ON MyTable
   ...>     FOR EACH ROW
   ...>     BEGIN UPDATE MyTable
   ...>         SET NOTES= new.Notes || ',DATEMODIFIED: ' || date('now')
   ...>         WHERE rowid=NEW.rowid;
   ...>     END;
sqlite>
sqlite>
sqlite>
sqlite>
sqlite> insert into mytable(Notes) Values('aa2');
sqlite> select * from mytable;
aa
aa2,DATEMODIFIED: 2014-12-22