SQLite Forum

How to write this trigger?

How to write this trigger?

(1) By anonymous on 2020-09-18 14:09:26 [link] [source]

I want to replace CF/LF with LF in notes field.

I created two triggers (one before insert and one before update) like so:

CREATE TRIGGER tgr before insert on t
  select new.notes = replace(new.notes,char(13)||char(10),char(10));

but they don't seem to work.

So, how do I do this?

BTW, is it possible to have the same trigger (like example above) assigned to both INSERT and UPDATE so as to not to have to write the same trigger twice?

(2) By Donald Griggs (dfgriggs) on 2020-09-18 18:48:22 in reply to 1 [source]

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.

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 Maybe this helps, Donald

(3) By anonymous on 2020-09-18 22:26:48 in reply to 2 [link] [source]

Hello, Eponymous,

It works for the INSERT trigger. Thank you.

Unless I turn off recursive_triggers with pragma recursive_triggers=on/off; at the global level, it fails on UPDATE trigger (same code) with Error: too many levels of trigger recursion.

pragma recursive_triggers=on/off; is not valid inside triggers, which would let me turn it off before the UPDATE statement, and turn it back on afterwards.

For my specific example, how do I prevent recursion inside the UPDATE trigger? Can I write the operation in a different way that does not cause recursion? Or, is the only option to have recursive triggers turned off all the time?

Is SELECT new.notes = replace... allowed syntax (I mean the assignment part) for SQLite3? I couldn't find it here or here.

My attempt did not work but it also did not produce any syntax errors.

I chose a BEFORE trigger because even though in my given example it wouldn't make a difference, in other cases where there is a CHECK constraint. wouldn't an AFTER trigger be too late if the unaltered value happened to violate the constraint? Or, will the constraint fire after the AFTER trigger?

For example, a phone number must be N-digits long including the country code. The trigger makes sure to normalize the local number to the correct number of digits. Will the AFTER trigger complete its job before the CHECK runs?

(4) By anonymous on 2020-09-18 22:58:51 in reply to 3 [link] [source]

OK, about the = I just realized it looks at it as equality :)

(5) By Keith Medcalf (kmedcalf) on 2020-09-19 06:19:33 in reply to 3 [link] [source]

Use the OF and WHEN clause in the trigger.


create trigger upd_t_x after update of x on t when new.x is not old.x
 update t set x = replace(x, 'this', 'that')
  where rowid == new.rowid;

Which will cause the trigger to fire if and only if the value of x is updated thus limiting the recursion.

(8) By anonymous on 2020-09-19 08:19:19 in reply to 5 [link] [source]

This worked. Thank you!

(6) By Keith Medcalf (kmedcalf) on 2020-09-19 06:30:08 in reply to 3 [link] [source]

A check constraint will run to ensure that the database contents are invariant (meet the check constraint).

So if two update statements are executed the check constraint is executed twice, once after each update.

(7.1) By Keith Medcalf (kmedcalf) on 2020-09-19 06:55:33 edited from 7.0 in reply to 6 [link] [source]

If you have, for example, an insert trigger that contains an update statement for the same row, then the check constraints will be run after the insert and again after the update.

Note that you obviously cannot update the row you are inserting in a before insert trigger since the row has not yet been inserted.

The purpose of a before trigger is to ensure that the database is in a state that will permit the operation to proceed or to be able to raise an appropriate error if the operation would violate business rules.

The purpose of an after trigger is to carry out concomitant changes to the database as a result of the successful completion (including having passed any constraints) of the operation firing the trigger.

(9) By anonymous on 2020-09-19 08:29:59 in reply to 7.1 [link] [source]

Note that you obviously cannot update the row you are inserting in a before insert trigger since the row has not yet been inserted.


I started out thinking in much less verbose solutions (and fewer execution steps) for such trivial data transformations that could be implemented in a BEFORE trigger as simply as:

SELECT new.field := function(new.field)

(not sure where/if I've seen this)


SELECT function(new.field) INTO new.field

(MySQL for @vars, not sure if it works in general for regular fields)

But neither syntax is supported by SQLite3.

Thanks again. Your suggestion appears to work.