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