SQLite triggers#

I wrote a Python script, triggers.py, to help me understand what data is available to SQLite triggers for which operations.

SQLite triggers are documented here. The key triggers are before and after for insert, update and delete. I have not explored instead of triggers yet, since those only apply to SQL views.

This section shows the output of running my triggers.py script. The triggers it uses are displayed at the bottom of the page.

1
create table no_pk (value text)
2
create table single_pk (id integer primary key, value text)
3
create table compound_pk (id1 integer, id2 integer, value text, primary key (id1, id2))
4

5
insert into no_pk (value) values (?) ('no_pk_value',)
6
  before insert on no_pk:
7
    {
8
      "action": "insert",
9
      "new": {
10
        "value": "no_pk_value"
11
      }
12
    }
13
  after insert on no_pk:
14
    {
15
      "action": "insert",
16
      "new": {
17
        "value": "no_pk_value"
18
      }
19
    }
20

21
insert into single_pk (id, value) values (?, ?) (1, 'single_pk_value')
22
  before insert on single_pk:
23
    {
24
      "action": "insert",
25
      "new": {
26
        "id": 1,
27
        "value": "single_pk_value"
28
      }
29
    }
30
  after insert on single_pk:
31
    {
32
      "action": "insert",
33
      "new": {
34
        "id": 1,
35
        "value": "single_pk_value"
36
      }
37
    }
38

39
insert or ignore:
40

41
insert or ignore into single_pk (id, value) values (?, ?) (1, 'single_pk_value_ignored')
42
  before insert on single_pk:
43
    {
44
      "action": "insert",
45
      "new": {
46
        "id": 1,
47
        "value": "single_pk_value_ignored"
48
      }
49
    }
50

51
insert or ignore into single_pk (id, value) values (?, ?) (2, 'single_pk_value_not_ignored')
52
  before insert on single_pk:
53
    {
54
      "action": "insert",
55
      "new": {
56
        "id": 2,
57
        "value": "single_pk_value_not_ignored"
58
      }
59
    }
60
  after insert on single_pk:
61
    {
62
      "action": "insert",
63
      "new": {
64
        "id": 2,
65
        "value": "single_pk_value_not_ignored"
66
      }
67
    }
68

69
insert or replace:
70

71
insert or replace into single_pk (id, value) values (?, ?) (1, 'single_pk_value')
72
  before insert on single_pk:
73
    {
74
      "action": "insert",
75
      "new": {
76
        "id": 1,
77
        "value": "single_pk_value"
78
      }
79
    }
80
  after insert on single_pk:
81
    {
82
      "action": "insert",
83
      "new": {
84
        "id": 1,
85
        "value": "single_pk_value"
86
      }
87
    }
88

89
insert or replace into single_pk (id, value) values (?, ?) (1, 'single_pk_value_replaced')
90
  before insert on single_pk:
91
    {
92
      "action": "insert",
93
      "new": {
94
        "id": 1,
95
        "value": "single_pk_value_replaced"
96
      }
97
    }
98
  after insert on single_pk:
99
    {
100
      "action": "insert",
101
      "new": {
102
        "id": 1,
103
        "value": "single_pk_value_replaced"
104
      }
105
    }
106

107
insert ... on conflict set (aka upsert):
108

109
insert into single_pk (id, value) values (?, ?) on conflict(id) do update set value=? (1, 'conflict_value', 'updated_on_conflict')
110
  before insert on single_pk:
111
    {
112
      "action": "insert",
113
      "new": {
114
        "id": 1,
115
        "value": "conflict_value"
116
      }
117
    }
118
  before update on single_pk:
119
    {
120
      "action": "update",
121
      "new": {
122
        "id": 1,
123
        "value": "updated_on_conflict"
124
      },
125
      "old": {
126
        "id": 1,
127
        "value": "single_pk_value_replaced"
128
      }
129
    }
130
  after update on single_pk:
131
    {
132
      "action": "update",
133
      "new": {
134
        "id": 1,
135
        "value": "updated_on_conflict"
136
      },
137
      "old": {
138
        "id": 1,
139
        "value": "single_pk_value_replaced"
140
      }
141
    }
142

143
insert into single_pk (id, value) values (?, ?) on conflict(id) do update set value=? (3, 'new_value', 'this_wont_be_used')
144
  before insert on single_pk:
145
    {
146
      "action": "insert",
147
      "new": {
148
        "id": 3,
149
        "value": "new_value"
150
      }
151
    }
152
  after insert on single_pk:
153
    {
154
      "action": "insert",
155
      "new": {
156
        "id": 3,
157
        "value": "new_value"
158
      }
159
    }
160

161
insert into compound_pk (id1, id2, value) values (?, ?, ?) (1, 2, 'compound_pk_value')
162
  before insert on compound_pk:
163
    {
164
      "action": "insert",
165
      "new": {
166
        "id1": 1,
167
        "id2": 2,
168
        "value": "compound_pk_value"
169
      }
170
    }
171
  after insert on compound_pk:
172
    {
173
      "action": "insert",
174
      "new": {
175
        "id1": 1,
176
        "id2": 2,
177
        "value": "compound_pk_value"
178
      }
179
    }
180

181
update no_pk set value = ? where rowid = ? ('no_pk_value_updated', 1)
182
  before update on no_pk:
183
    {
184
      "action": "update",
185
      "new": {
186
        "value": "no_pk_value_updated"
187
      },
188
      "old": {
189
        "value": "no_pk_value"
190
      }
191
    }
192
  after update on no_pk:
193
    {
194
      "action": "update",
195
      "new": {
196
        "value": "no_pk_value_updated"
197
      },
198
      "old": {
199
        "value": "no_pk_value"
200
      }
201
    }
202

203
update single_pk set value = ? where id = ? ('single_pk_value_updated', 1)
204
  before update on single_pk:
205
    {
206
      "action": "update",
207
      "new": {
208
        "id": 1,
209
        "value": "single_pk_value_updated"
210
      },
211
      "old": {
212
        "id": 1,
213
        "value": "updated_on_conflict"
214
      }
215
    }
216
  after update on single_pk:
217
    {
218
      "action": "update",
219
      "new": {
220
        "id": 1,
221
        "value": "single_pk_value_updated"
222
      },
223
      "old": {
224
        "id": 1,
225
        "value": "updated_on_conflict"
226
      }
227
    }
228

229
update compound_pk set value = ? where id1 = ? and id2 = ? ('compound_pk_value_updated', 1, 2)
230
  before update on compound_pk:
231
    {
232
      "action": "update",
233
      "new": {
234
        "id1": 1,
235
        "id2": 2,
236
        "value": "compound_pk_value_updated"
237
      },
238
      "old": {
239
        "id1": 1,
240
        "id2": 2,
241
        "value": "compound_pk_value"
242
      }
243
    }
244
  after update on compound_pk:
245
    {
246
      "action": "update",
247
      "new": {
248
        "id1": 1,
249
        "id2": 2,
250
        "value": "compound_pk_value_updated"
251
      },
252
      "old": {
253
        "id1": 1,
254
        "id2": 2,
255
        "value": "compound_pk_value"
256
      }
257
    }
258

259
delete from no_pk where rowid = ? (1,)
260
  before delete on no_pk:
261
    {
262
      "action": "delete",
263
      "old": {
264
        "value": "no_pk_value_updated"
265
      }
266
    }
267
  after delete on no_pk:
268
    {
269
      "action": "delete",
270
      "old": {
271
        "value": "no_pk_value_updated"
272
      }
273
    }
274

275
delete from single_pk where id = ? (1,)
276
  before delete on single_pk:
277
    {
278
      "action": "delete",
279
      "old": {
280
        "id": 1,
281
        "value": "single_pk_value_updated"
282
      }
283
    }
284
  after delete on single_pk:
285
    {
286
      "action": "delete",
287
      "old": {
288
        "id": 1,
289
        "value": "single_pk_value_updated"
290
      }
291
    }
292

293
delete from compound_pk where id1 = ? and id2 = ? (1, 2)
294
  before delete on compound_pk:
295
    {
296
      "action": "delete",
297
      "old": {
298
        "id1": 1,
299
        "id2": 2,
300
        "value": "compound_pk_value_updated"
301
      }
302
    }
303
  after delete on compound_pk:
304
    {
305
      "action": "delete",
306
      "old": {
307
        "id1": 1,
308
        "id2": 2,
309
        "value": "compound_pk_value_updated"
310
      }
311
    }

The triggers it uses#

1
CREATE TRIGGER no_pk_bi
2
    before insert on no_pk
3
    for each row
4
    begin
5
      insert into log (trigger_name, table_name, details)
6
      values ('before insert', 'no_pk', json_object('action', 'insert', 'new', json_object('value', NEW.value)));
7
    end;
8

9
CREATE TRIGGER no_pk_ai
10
    after insert on no_pk
11
    for each row
12
    begin
13
      insert into log (trigger_name, table_name, details)
14
      values ('after insert', 'no_pk', json_object('action', 'insert', 'new', json_object('value', NEW.value)));
15
    end;
16

17
CREATE TRIGGER no_pk_bu
18
    before update on no_pk
19
    for each row
20
    begin
21
      insert into log (trigger_name, table_name, details)
22
      values ('before update', 'no_pk', json_object('action', 'update', 'new', json_object('value', NEW.value), 'old', json_object('value', OLD.value)));
23
    end;
24

25
CREATE TRIGGER no_pk_au
26
    after update on no_pk
27
    for each row
28
    begin
29
      insert into log (trigger_name, table_name, details)
30
      values ('after update', 'no_pk', json_object('action', 'update', 'new', json_object('value', NEW.value), 'old', json_object('value', OLD.value)));
31
    end;
32

33
CREATE TRIGGER no_pk_bd
34
    before delete on no_pk
35
    for each row
36
    begin
37
      insert into log (trigger_name, table_name, details)
38
      values ('before delete', 'no_pk', json_object('action', 'delete', 'old', json_object('value', OLD.value)));
39
    end;
40

41
CREATE TRIGGER no_pk_ad
42
    after delete on no_pk
43
    for each row
44
    begin
45
      insert into log (trigger_name, table_name, details)
46
      values ('after delete', 'no_pk', json_object('action', 'delete', 'old', json_object('value', OLD.value)));
47
    end;
48

49
CREATE TRIGGER single_pk_bi
50
    before insert on single_pk
51
    for each row
52
    begin
53
      insert into log (trigger_name, table_name, details)
54
      values ('before insert', 'single_pk', json_object('action', 'insert', 'new', json_object('id', NEW.id, 'value', NEW.value)));
55
    end;
56

57
CREATE TRIGGER single_pk_ai
58
    after insert on single_pk
59
    for each row
60
    begin
61
      insert into log (trigger_name, table_name, details)
62
      values ('after insert', 'single_pk', json_object('action', 'insert', 'new', json_object('id', NEW.id, 'value', NEW.value)));
63
    end;
64

65
CREATE TRIGGER single_pk_bu
66
    before update on single_pk
67
    for each row
68
    begin
69
      insert into log (trigger_name, table_name, details)
70
      values ('before update', 'single_pk', json_object('action', 'update', 'new', json_object('id', NEW.id, 'value', NEW.value), 'old', json_object('id', OLD.id, 'value', OLD.value)));
71
    end;
72

73
CREATE TRIGGER single_pk_au
74
    after update on single_pk
75
    for each row
76
    begin
77
      insert into log (trigger_name, table_name, details)
78
      values ('after update', 'single_pk', json_object('action', 'update', 'new', json_object('id', NEW.id, 'value', NEW.value), 'old', json_object('id', OLD.id, 'value', OLD.value)));
79
    end;
80

81
CREATE TRIGGER single_pk_bd
82
    before delete on single_pk
83
    for each row
84
    begin
85
      insert into log (trigger_name, table_name, details)
86
      values ('before delete', 'single_pk', json_object('action', 'delete', 'old', json_object('id', OLD.id, 'value', OLD.value)));
87
    end;
88

89
CREATE TRIGGER single_pk_ad
90
    after delete on single_pk
91
    for each row
92
    begin
93
      insert into log (trigger_name, table_name, details)
94
      values ('after delete', 'single_pk', json_object('action', 'delete', 'old', json_object('id', OLD.id, 'value', OLD.value)));
95
    end;
96

97
CREATE TRIGGER compound_pk_bi
98
    before insert on compound_pk
99
    for each row
100
    begin
101
      insert into log (trigger_name, table_name, details)
102
      values ('before insert', 'compound_pk', json_object('action', 'insert', 'new', json_object('id1', NEW.id1, 'id2', NEW.id2, 'value', NEW.value)));
103
    end;
104

105
CREATE TRIGGER compound_pk_ai
106
    after insert on compound_pk
107
    for each row
108
    begin
109
      insert into log (trigger_name, table_name, details)
110
      values ('after insert', 'compound_pk', json_object('action', 'insert', 'new', json_object('id1', NEW.id1, 'id2', NEW.id2, 'value', NEW.value)));
111
    end;
112

113
CREATE TRIGGER compound_pk_bu
114
    before update on compound_pk
115
    for each row
116
    begin
117
      insert into log (trigger_name, table_name, details)
118
      values ('before update', 'compound_pk', json_object('action', 'update', 'new', json_object('id1', NEW.id1, 'id2', NEW.id2, 'value', NEW.value), 'old', json_object('id1', OLD.id1, 'id2', OLD.id2, 'value', OLD.value)));
119
    end;
120

121
CREATE TRIGGER compound_pk_au
122
    after update on compound_pk
123
    for each row
124
    begin
125
      insert into log (trigger_name, table_name, details)
126
      values ('after update', 'compound_pk', json_object('action', 'update', 'new', json_object('id1', NEW.id1, 'id2', NEW.id2, 'value', NEW.value), 'old', json_object('id1', OLD.id1, 'id2', OLD.id2, 'value', OLD.value)));
127
    end;
128

129
CREATE TRIGGER compound_pk_bd
130
    before delete on compound_pk
131
    for each row
132
    begin
133
      insert into log (trigger_name, table_name, details)
134
      values ('before delete', 'compound_pk', json_object('action', 'delete', 'old', json_object('id1', OLD.id1, 'id2', OLD.id2, 'value', OLD.value)));
135
    end;
136

137
CREATE TRIGGER compound_pk_ad
138
    after delete on compound_pk
139
    for each row
140
    begin
141
      insert into log (trigger_name, table_name, details)
142
      values ('after delete', 'compound_pk', json_object('action', 'delete', 'old', json_object('id1', OLD.id1, 'id2', OLD.id2, 'value', OLD.value)));
143
    end;

Rebuilding this page#

This page uses Cog to include the output of the triggers.py script. To rebuild this page after modifying the script, run this:

1
cog -P -r sqlite-triggers.md