SQLite Forum

How to write this trigger?
Login
Hello, Anonymous,

1. I think you want an AFTER trigger.  See <https://sqlite.org/lang_createtrigger.html#cautions_on_the_use_of_before_triggers>
2. I wonder if you wanted an UPDATE rather than a SELECT
3. You don't want the trigger to alter the entire table with every insert, so you likely want a WHERE clause.
4. AFAIK, you must write two triggers if they should fire on both INSERT and UPDATE.

In the example below, I used visible characters instead of CR/LF to avoid confusion.  I inserted two rows before we even created the trigger -- just to prove we aren't updating the entire table with each firing.

<code>
SQLite version 3.32.3 2020-06-18 14:00:33
sqlite> CREATE TEMP TABLE t (notes TEXT);
sqlite> /* Insert two rows before we create the trigger */
sqlite> INSERT INTO t VALUES ('CAFE'), ('BOBCAT');    
sqlite> CREATE TRIGGER tgr AFTER insert on t /*changes 'CA' to 'WHA' */
   ...> begin
   ...>   UPDATE t SET notes = replace(new.notes, 'C'||'A',  'WHA')
   ...>   WHERE ROWID = new.ROWID;
   ...> end;
sqlite>
sqlite> INSERT INTO t VALUES ('CAT');
sqlite> SELECT ROWID, * FROM t;
1|CAFE
2|BOBCAT
3|WHAT
<code>
Maybe this helps,
   Donald