xref: /onnv-gate/usr/src/lib/libsqlite/test/trigger1.test (revision 4520:7dbeadedd7fe)
1*4520Snw141292
2*4520Snw141292#pragma ident	"%Z%%M%	%I%	%E% SMI"
3*4520Snw141292
4*4520Snw141292# The author disclaims copyright to this source code.  In place of
5*4520Snw141292# a legal notice, here is a blessing:
6*4520Snw141292#
7*4520Snw141292#    May you do good and not evil.
8*4520Snw141292#    May you find forgiveness for yourself and forgive others.
9*4520Snw141292#    May you share freely, never taking more than you give.
10*4520Snw141292#
11*4520Snw141292#***********************************************************************
12*4520Snw141292#
13*4520Snw141292# This file tests creating and dropping triggers, and interaction thereof
14*4520Snw141292# with the database COMMIT/ROLLBACK logic.
15*4520Snw141292#
16*4520Snw141292# 1. CREATE and DROP TRIGGER tests
17*4520Snw141292# trig-1.1: Error if table does not exist
18*4520Snw141292# trig-1.2: Error if trigger already exists
19*4520Snw141292# trig-1.3: Created triggers are deleted if the transaction is rolled back
20*4520Snw141292# trig-1.4: DROP TRIGGER removes trigger
21*4520Snw141292# trig-1.5: Dropped triggers are restored if the transaction is rolled back
22*4520Snw141292# trig-1.6: Error if dropped trigger doesn't exist
23*4520Snw141292# trig-1.7: Dropping the table automatically drops all triggers
24*4520Snw141292# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
25*4520Snw141292# trig-1.9: Ensure that we cannot create a trigger on sqlite_master
26*4520Snw141292# trig-1.10:
27*4520Snw141292# trig-1.11:
28*4520Snw141292# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
29*4520Snw141292# trig-1.13: Ensure that AFTER triggers cannot be created on views
30*4520Snw141292# trig-1.14: Ensure that BEFORE triggers cannot be created on views
31*4520Snw141292#
32*4520Snw141292
33*4520Snw141292set testdir [file dirname $argv0]
34*4520Snw141292source $testdir/tester.tcl
35*4520Snw141292
36*4520Snw141292do_test trigger1-1.1.2 {
37*4520Snw141292   catchsql {
38*4520Snw141292     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
39*4520Snw141292       SELECT * from sqlite_master;
40*4520Snw141292     END;
41*4520Snw141292   }
42*4520Snw141292} {1 {no such table: no_such_table}}
43*4520Snw141292do_test trigger1-1.1.2 {
44*4520Snw141292   catchsql {
45*4520Snw141292     CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
46*4520Snw141292       SELECT * from sqlite_master;
47*4520Snw141292     END;
48*4520Snw141292   }
49*4520Snw141292} {1 {no such table: no_such_table}}
50*4520Snw141292
51*4520Snw141292execsql {
52*4520Snw141292    CREATE TABLE t1(a);
53*4520Snw141292}
54*4520Snw141292execsql {
55*4520Snw141292	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
56*4520Snw141292	  INSERT INTO t1 values(1);
57*4520Snw141292 	END;
58*4520Snw141292}
59*4520Snw141292do_test trigger1-1.2 {
60*4520Snw141292    catchsql {
61*4520Snw141292	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
62*4520Snw141292	    SELECT * FROM sqlite_master;
63*4520Snw141292 	END
64*4520Snw141292     }
65*4520Snw141292} {1 {trigger tr1 already exists}}
66*4520Snw141292
67*4520Snw141292do_test trigger1-1.3 {
68*4520Snw141292    catchsql {
69*4520Snw141292	BEGIN;
70*4520Snw141292	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
71*4520Snw141292	    SELECT * from sqlite_master; END;
72*4520Snw141292        ROLLBACK;
73*4520Snw141292	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
74*4520Snw141292	    SELECT * from sqlite_master; END;
75*4520Snw141292    }
76*4520Snw141292} {0 {}}
77*4520Snw141292
78*4520Snw141292do_test trigger1-1.4 {
79*4520Snw141292    catchsql {
80*4520Snw141292	DROP TRIGGER tr1;
81*4520Snw141292	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
82*4520Snw141292	    SELECT * FROM sqlite_master;
83*4520Snw141292	END
84*4520Snw141292    }
85*4520Snw141292} {0 {}}
86*4520Snw141292
87*4520Snw141292do_test trigger1-1.5 {
88*4520Snw141292    execsql {
89*4520Snw141292	BEGIN;
90*4520Snw141292	DROP TRIGGER tr2;
91*4520Snw141292	ROLLBACK;
92*4520Snw141292	DROP TRIGGER tr2;
93*4520Snw141292    }
94*4520Snw141292} {}
95*4520Snw141292
96*4520Snw141292do_test trigger1-1.6 {
97*4520Snw141292    catchsql {
98*4520Snw141292	DROP TRIGGER biggles;
99*4520Snw141292    }
100*4520Snw141292} {1 {no such trigger: biggles}}
101*4520Snw141292
102*4520Snw141292do_test trigger1-1.7 {
103*4520Snw141292    catchsql {
104*4520Snw141292	DROP TABLE t1;
105*4520Snw141292	DROP TRIGGER tr1;
106*4520Snw141292    }
107*4520Snw141292} {1 {no such trigger: tr1}}
108*4520Snw141292
109*4520Snw141292execsql {
110*4520Snw141292  CREATE TEMP TABLE temp_table(a);
111*4520Snw141292}
112*4520Snw141292do_test trigger1-1.8 {
113*4520Snw141292  execsql {
114*4520Snw141292	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
115*4520Snw141292	    SELECT * from sqlite_master;
116*4520Snw141292	END;
117*4520Snw141292	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
118*4520Snw141292  }
119*4520Snw141292} {0}
120*4520Snw141292
121*4520Snw141292do_test trigger1-1.9 {
122*4520Snw141292  catchsql {
123*4520Snw141292    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
124*4520Snw141292       SELECT * FROM sqlite_master;
125*4520Snw141292    END;
126*4520Snw141292  }
127*4520Snw141292} {1 {cannot create trigger on system table}}
128*4520Snw141292
129*4520Snw141292# Check to make sure that a DELETE statement within the body of
130*4520Snw141292# a trigger does not mess up the DELETE that caused the trigger to
131*4520Snw141292# run in the first place.
132*4520Snw141292#
133*4520Snw141292do_test trigger1-1.10 {
134*4520Snw141292  execsql {
135*4520Snw141292    create table t1(a,b);
136*4520Snw141292    insert into t1 values(1,'a');
137*4520Snw141292    insert into t1 values(2,'b');
138*4520Snw141292    insert into t1 values(3,'c');
139*4520Snw141292    insert into t1 values(4,'d');
140*4520Snw141292    create trigger r1 after delete on t1 for each row begin
141*4520Snw141292      delete from t1 WHERE a=old.a+2;
142*4520Snw141292    end;
143*4520Snw141292    delete from t1 where a in (1,3);
144*4520Snw141292    select * from t1;
145*4520Snw141292    drop table t1;
146*4520Snw141292  }
147*4520Snw141292} {2 b 4 d}
148*4520Snw141292do_test trigger1-1.11 {
149*4520Snw141292  execsql {
150*4520Snw141292    create table t1(a,b);
151*4520Snw141292    insert into t1 values(1,'a');
152*4520Snw141292    insert into t1 values(2,'b');
153*4520Snw141292    insert into t1 values(3,'c');
154*4520Snw141292    insert into t1 values(4,'d');
155*4520Snw141292    create trigger r1 after update on t1 for each row begin
156*4520Snw141292      delete from t1 WHERE a=old.a+2;
157*4520Snw141292    end;
158*4520Snw141292    update t1 set b='x-' || b where a in (1,3);
159*4520Snw141292    select * from t1;
160*4520Snw141292    drop table t1;
161*4520Snw141292  }
162*4520Snw141292} {1 x-a 2 b 4 d}
163*4520Snw141292
164*4520Snw141292# Ensure that we cannot create INSTEAD OF triggers on tables
165*4520Snw141292do_test trigger1-1.12 {
166*4520Snw141292  catchsql {
167*4520Snw141292    create table t1(a,b);
168*4520Snw141292    create trigger t1t instead of update on t1 for each row begin
169*4520Snw141292      delete from t1 WHERE a=old.a+2;
170*4520Snw141292    end;
171*4520Snw141292  }
172*4520Snw141292} {1 {cannot create INSTEAD OF trigger on table: t1}}
173*4520Snw141292# Ensure that we cannot create BEFORE triggers on views
174*4520Snw141292do_test trigger1-1.13 {
175*4520Snw141292  catchsql {
176*4520Snw141292    create view v1 as select * from t1;
177*4520Snw141292    create trigger v1t before update on v1 for each row begin
178*4520Snw141292      delete from t1 WHERE a=old.a+2;
179*4520Snw141292    end;
180*4520Snw141292  }
181*4520Snw141292} {1 {cannot create BEFORE trigger on view: v1}}
182*4520Snw141292# Ensure that we cannot create AFTER triggers on views
183*4520Snw141292do_test trigger1-1.14 {
184*4520Snw141292  catchsql {
185*4520Snw141292    drop view v1;
186*4520Snw141292    create view v1 as select * from t1;
187*4520Snw141292    create trigger v1t AFTER update on v1 for each row begin
188*4520Snw141292      delete from t1 WHERE a=old.a+2;
189*4520Snw141292    end;
190*4520Snw141292  }
191*4520Snw141292} {1 {cannot create AFTER trigger on view: v1}}
192*4520Snw141292
193*4520Snw141292# Check for memory leaks in the trigger parser
194*4520Snw141292#
195*4520Snw141292do_test trigger1-2.1 {
196*4520Snw141292  catchsql {
197*4520Snw141292    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
198*4520Snw141292      SELECT * FROM;  -- Syntax error
199*4520Snw141292    END;
200*4520Snw141292  }
201*4520Snw141292} {1 {near ";": syntax error}}
202*4520Snw141292do_test trigger1-2.2 {
203*4520Snw141292  catchsql {
204*4520Snw141292    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
205*4520Snw141292      SELECT * FROM t1;
206*4520Snw141292      SELECT * FROM;  -- Syntax error
207*4520Snw141292    END;
208*4520Snw141292  }
209*4520Snw141292} {1 {near ";": syntax error}}
210*4520Snw141292
211*4520Snw141292# Create a trigger that refers to a table that might not exist.
212*4520Snw141292#
213*4520Snw141292do_test trigger1-3.1 {
214*4520Snw141292  execsql {
215*4520Snw141292    CREATE TEMP TABLE t2(x,y);
216*4520Snw141292  }
217*4520Snw141292  catchsql {
218*4520Snw141292    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
219*4520Snw141292      INSERT INTO t2 VALUES(NEW.a,NEW.b);
220*4520Snw141292    END;
221*4520Snw141292  }
222*4520Snw141292} {0 {}}
223*4520Snw141292do_test trigger-3.2 {
224*4520Snw141292  catchsql {
225*4520Snw141292    INSERT INTO t1 VALUES(1,2);
226*4520Snw141292    SELECT * FROM t2;
227*4520Snw141292  }
228*4520Snw141292} {1 {no such table: main.t2}}
229*4520Snw141292do_test trigger-3.3 {
230*4520Snw141292  db close
231*4520Snw141292  set rc [catch {sqlite db test.db} err]
232*4520Snw141292  if {$rc} {lappend rc $err}
233*4520Snw141292  set rc
234*4520Snw141292} {0}
235*4520Snw141292do_test trigger-3.4 {
236*4520Snw141292  catchsql {
237*4520Snw141292    INSERT INTO t1 VALUES(1,2);
238*4520Snw141292    SELECT * FROM t2;
239*4520Snw141292  }
240*4520Snw141292} {1 {no such table: main.t2}}
241*4520Snw141292do_test trigger-3.5 {
242*4520Snw141292  catchsql {
243*4520Snw141292    CREATE TEMP TABLE t2(x,y);
244*4520Snw141292    INSERT INTO t1 VALUES(1,2);
245*4520Snw141292    SELECT * FROM t2;
246*4520Snw141292  }
247*4520Snw141292} {1 {no such table: main.t2}}
248*4520Snw141292do_test trigger-3.6 {
249*4520Snw141292  catchsql {
250*4520Snw141292    DROP TRIGGER r1;
251*4520Snw141292    CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
252*4520Snw141292      INSERT INTO t2 VALUES(NEW.a,NEW.b);
253*4520Snw141292    END;
254*4520Snw141292    INSERT INTO t1 VALUES(1,2);
255*4520Snw141292    SELECT * FROM t2;
256*4520Snw141292  }
257*4520Snw141292} {0 {1 2}}
258*4520Snw141292do_test trigger-3.7 {
259*4520Snw141292  execsql {
260*4520Snw141292    DROP TABLE t2;
261*4520Snw141292    CREATE TABLE t2(x,y);
262*4520Snw141292    SELECT * FROM t2;
263*4520Snw141292  }
264*4520Snw141292} {}
265*4520Snw141292do_test trigger-3.8 {
266*4520Snw141292  execsql {
267*4520Snw141292    INSERT INTO t1 VALUES(3,4);
268*4520Snw141292    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
269*4520Snw141292  }
270*4520Snw141292} {1 2 3 4 3 4}
271*4520Snw141292do_test trigger-3.9 {
272*4520Snw141292  db close
273*4520Snw141292  sqlite db test.db
274*4520Snw141292  execsql {
275*4520Snw141292    INSERT INTO t1 VALUES(5,6);
276*4520Snw141292    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
277*4520Snw141292  }
278*4520Snw141292} {1 2 3 4 5 6 3 4}
279*4520Snw141292
280*4520Snw141292do_test trigger-4.1 {
281*4520Snw141292  execsql {
282*4520Snw141292    CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
283*4520Snw141292      INSERT INTO t2 VALUES(NEW.a,NEW.b);
284*4520Snw141292    END;
285*4520Snw141292    INSERT INTO t1 VALUES(7,8);
286*4520Snw141292    SELECT * FROM t2;
287*4520Snw141292  }
288*4520Snw141292} {3 4 7 8}
289*4520Snw141292do_test trigger-4.2 {
290*4520Snw141292  sqlite db2 test.db
291*4520Snw141292  execsql {
292*4520Snw141292    INSERT INTO t1 VALUES(9,10);
293*4520Snw141292  } db2;
294*4520Snw141292  db2 close
295*4520Snw141292  execsql {
296*4520Snw141292    SELECT * FROM t2;
297*4520Snw141292  }
298*4520Snw141292} {3 4 7 8}
299*4520Snw141292do_test trigger-4.3 {
300*4520Snw141292  execsql {
301*4520Snw141292    DROP TABLE t1;
302*4520Snw141292    SELECT * FROM t2;
303*4520Snw141292  };
304*4520Snw141292} {3 4 7 8}
305*4520Snw141292do_test trigger-4.4 {
306*4520Snw141292  db close
307*4520Snw141292  sqlite db test.db
308*4520Snw141292  execsql {
309*4520Snw141292    SELECT * FROM t2;
310*4520Snw141292  };
311*4520Snw141292} {3 4 7 8}
312*4520Snw141292
313*4520Snw141292integrity_check trigger-5.1
314*4520Snw141292
315*4520Snw141292# Create a trigger with the same name as a table.  Make sure the
316*4520Snw141292# trigger works.  Then drop the trigger.  Make sure the table is
317*4520Snw141292# still there.
318*4520Snw141292#
319*4520Snw141292do_test trigger-6.1 {
320*4520Snw141292  execsql {SELECT type, name FROM sqlite_master}
321*4520Snw141292} {view v1 table t2}
322*4520Snw141292do_test trigger-6.2 {
323*4520Snw141292  execsql {
324*4520Snw141292    CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
325*4520Snw141292      SELECT RAISE(ABORT,'deletes are not allows');
326*4520Snw141292    END;
327*4520Snw141292    SELECT type, name FROM sqlite_master;
328*4520Snw141292  }
329*4520Snw141292} {view v1 table t2 trigger t2}
330*4520Snw141292do_test trigger-6.3 {
331*4520Snw141292  catchsql {DELETE FROM t2}
332*4520Snw141292} {1 {deletes are not allows}}
333*4520Snw141292do_test trigger-6.4 {
334*4520Snw141292  execsql {SELECT * FROM t2}
335*4520Snw141292} {3 4 7 8}
336*4520Snw141292do_test trigger-6.5 {
337*4520Snw141292  db close
338*4520Snw141292  sqlite db test.db
339*4520Snw141292  execsql {SELECT type, name FROM sqlite_master}
340*4520Snw141292} {view v1 table t2 trigger t2}
341*4520Snw141292do_test trigger-6.6 {
342*4520Snw141292  execsql {
343*4520Snw141292    DROP TRIGGER t2;
344*4520Snw141292    SELECT type, name FROM sqlite_master;
345*4520Snw141292  }
346*4520Snw141292} {view v1 table t2}
347*4520Snw141292do_test trigger-6.7 {
348*4520Snw141292  execsql {SELECT * FROM t2}
349*4520Snw141292} {3 4 7 8}
350*4520Snw141292do_test trigger-6.8 {
351*4520Snw141292  db close
352*4520Snw141292  sqlite db test.db
353*4520Snw141292  execsql {SELECT * FROM t2}
354*4520Snw141292} {3 4 7 8}
355*4520Snw141292
356*4520Snw141292integrity_check trigger-7.1
357*4520Snw141292
358*4520Snw141292# Check to make sure the name of a trigger can be quoted so that keywords
359*4520Snw141292# can be used as trigger names.  Ticket #468
360*4520Snw141292#
361*4520Snw141292do_test trigger-8.1 {
362*4520Snw141292  execsql {
363*4520Snw141292    CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
364*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
365*4520Snw141292  }
366*4520Snw141292} {trigger}
367*4520Snw141292do_test trigger-8.2 {
368*4520Snw141292  execsql {
369*4520Snw141292    DROP TRIGGER 'trigger';
370*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
371*4520Snw141292  }
372*4520Snw141292} {}
373*4520Snw141292do_test trigger-8.3 {
374*4520Snw141292  execsql {
375*4520Snw141292    CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
376*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
377*4520Snw141292  }
378*4520Snw141292} {trigger}
379*4520Snw141292do_test trigger-8.4 {
380*4520Snw141292  execsql {
381*4520Snw141292    DROP TRIGGER "trigger";
382*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
383*4520Snw141292  }
384*4520Snw141292} {}
385*4520Snw141292do_test trigger-8.5 {
386*4520Snw141292  execsql {
387*4520Snw141292    CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
388*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
389*4520Snw141292  }
390*4520Snw141292} {trigger}
391*4520Snw141292do_test trigger-8.6 {
392*4520Snw141292  execsql {
393*4520Snw141292    DROP TRIGGER [trigger];
394*4520Snw141292    SELECT name FROM sqlite_master WHERE type='trigger';
395*4520Snw141292  }
396*4520Snw141292} {}
397*4520Snw141292
398*4520Snw141292# Make sure REPLACE works inside of triggers.
399*4520Snw141292#
400*4520Snw141292do_test trigger-9.1 {
401*4520Snw141292  execsql {
402*4520Snw141292    CREATE TABLE t3(a,b);
403*4520Snw141292    CREATE TABLE t4(x UNIQUE, b);
404*4520Snw141292    CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
405*4520Snw141292      REPLACE INTO t4 VALUES(new.a,new.b);
406*4520Snw141292    END;
407*4520Snw141292    INSERT INTO t3 VALUES(1,2);
408*4520Snw141292    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
409*4520Snw141292  }
410*4520Snw141292} {1 2 99 99 1 2}
411*4520Snw141292do_test trigger-9.2 {
412*4520Snw141292  execsql {
413*4520Snw141292    INSERT INTO t3 VALUES(1,3);
414*4520Snw141292    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
415*4520Snw141292  }
416*4520Snw141292} {1 2 1 3 99 99 1 3}
417*4520Snw141292
418*4520Snw141292execsql {
419*4520Snw141292  DROP TABLE t2;
420*4520Snw141292  DROP TABLE t3;
421*4520Snw141292  DROP TABLE t4;
422*4520Snw141292}
423*4520Snw141292
424*4520Snw141292# Ticket #764. At one stage TEMP triggers would fail to re-install when the
425*4520Snw141292# schema was reloaded. The following tests ensure that TEMP triggers are
426*4520Snw141292# correctly re-installed.
427*4520Snw141292#
428*4520Snw141292# Also verify that references within trigger programs are resolved at
429*4520Snw141292# statement compile time, not trigger installation time. This means, for
430*4520Snw141292# example, that you can drop and re-create tables referenced by triggers.
431*4520Snw141292do_test trigger-10.0 {
432*4520Snw141292  file delete -force test2.db
433*4520Snw141292  file delete -force test2.db-journal
434*4520Snw141292  sqlite db2 test2.db
435*4520Snw141292  execsql {CREATE TABLE t3(a, b, c);} db2
436*4520Snw141292  db2 close
437*4520Snw141292  execsql {
438*4520Snw141292    ATTACH 'test2.db' AS aux;
439*4520Snw141292  }
440*4520Snw141292} {}
441*4520Snw141292do_test trigger-10.1 {
442*4520Snw141292  execsql {
443*4520Snw141292    CREATE TABLE t1(a, b, c);
444*4520Snw141292    CREATE temp TABLE t2(a, b, c);
445*4520Snw141292    CREATE TABLE insert_log(db, a, b, c);
446*4520Snw141292  }
447*4520Snw141292} {}
448*4520Snw141292do_test trigger-10.2 {
449*4520Snw141292  execsql {
450*4520Snw141292    CREATE TEMP TRIGGER trig1 AFTER INSERT ON t1 BEGIN
451*4520Snw141292      INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
452*4520Snw141292    END;
453*4520Snw141292    CREATE TEMP TRIGGER trig2 AFTER INSERT ON t2 BEGIN
454*4520Snw141292      INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
455*4520Snw141292    END;
456*4520Snw141292    CREATE TEMP TRIGGER trig3 AFTER INSERT ON t3 BEGIN
457*4520Snw141292      INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
458*4520Snw141292    END;
459*4520Snw141292  }
460*4520Snw141292} {}
461*4520Snw141292do_test trigger-10.3 {
462*4520Snw141292  execsql {
463*4520Snw141292    INSERT INTO t1 VALUES(1, 2, 3);
464*4520Snw141292    INSERT INTO t2 VALUES(4, 5, 6);
465*4520Snw141292    INSERT INTO t3  VALUES(7, 8, 9);
466*4520Snw141292  }
467*4520Snw141292} {}
468*4520Snw141292do_test trigger-10.4 {
469*4520Snw141292  execsql {
470*4520Snw141292    SELECT * FROM insert_log;
471*4520Snw141292  }
472*4520Snw141292} {main 1 2 3 temp 4 5 6 aux 7 8 9}
473*4520Snw141292do_test trigger-10.5 {
474*4520Snw141292  execsql {
475*4520Snw141292    BEGIN;
476*4520Snw141292    INSERT INTO t1 VALUES(1, 2, 3);
477*4520Snw141292    INSERT INTO t2 VALUES(4, 5, 6);
478*4520Snw141292    INSERT INTO t3  VALUES(7, 8, 9);
479*4520Snw141292    ROLLBACK;
480*4520Snw141292  }
481*4520Snw141292} {}
482*4520Snw141292do_test trigger-10.6 {
483*4520Snw141292  execsql {
484*4520Snw141292    SELECT * FROM insert_log;
485*4520Snw141292  }
486*4520Snw141292} {main 1 2 3 temp 4 5 6 aux 7 8 9}
487*4520Snw141292do_test trigger-10.7 {
488*4520Snw141292  execsql {
489*4520Snw141292    DELETE FROM insert_log;
490*4520Snw141292    INSERT INTO t1 VALUES(11, 12, 13);
491*4520Snw141292    INSERT INTO t2 VALUES(14, 15, 16);
492*4520Snw141292    INSERT INTO t3 VALUES(17, 18, 19);
493*4520Snw141292  }
494*4520Snw141292} {}
495*4520Snw141292do_test trigger-10.8 {
496*4520Snw141292  execsql {
497*4520Snw141292    SELECT * FROM insert_log;
498*4520Snw141292  }
499*4520Snw141292} {main 11 12 13 temp 14 15 16 aux 17 18 19}
500*4520Snw141292do_test trigger-10.8 {
501*4520Snw141292# Drop and re-create the insert_log table in a different database. Note
502*4520Snw141292# that we can change the column names because the trigger programs don't
503*4520Snw141292# use them explicitly.
504*4520Snw141292  execsql {
505*4520Snw141292    DROP TABLE insert_log;
506*4520Snw141292    CREATE temp TABLE insert_log(db, d, e, f);
507*4520Snw141292  }
508*4520Snw141292} {}
509*4520Snw141292do_test trigger-10.10 {
510*4520Snw141292  execsql {
511*4520Snw141292    INSERT INTO t1 VALUES(21, 22, 23);
512*4520Snw141292    INSERT INTO t2 VALUES(24, 25, 26);
513*4520Snw141292    INSERT INTO t3  VALUES(27, 28, 29);
514*4520Snw141292  }
515*4520Snw141292} {}
516*4520Snw141292do_test trigger-10.11 {
517*4520Snw141292  execsql {
518*4520Snw141292    SELECT * FROM insert_log;
519*4520Snw141292  }
520*4520Snw141292} {main 21 22 23 temp 24 25 26 aux 27 28 29}
521*4520Snw141292
522*4520Snw141292finish_test
523