mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 20:20:20 +00:00
82 lines
2.1 KiB
Plaintext
82 lines
2.1 KiB
Plaintext
|
|
CREATE TABLE t17a(ii INT);
|
|
CREATE TABLE t17b(tt TEXT PRIMARY KEY, ss);
|
|
CREATE TRIGGER t17a_ai AFTER INSERT ON t17a BEGIN
|
|
INSERT INTO t17b(tt) VALUES(new.ii);
|
|
END;
|
|
CREATE TRIGGER t17b_ai AFTER INSERT ON t17b BEGIN
|
|
UPDATE t17b SET ss = 4;
|
|
END;
|
|
INSERT INTO t17a(ii) VALUES('1');
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
CREATE TABLE t18(a PRIMARY KEY,b,c);
|
|
INSERT INTO t18(a,b,c) VALUES(1,2,3);
|
|
CREATE TRIGGER t18r1 BEFORE UPDATE ON t18 BEGIN
|
|
UPDATE t18 SET b=1000 WHERE a=old.a;
|
|
END;
|
|
UPDATE t18 SET c=b WHERE a=1;
|
|
SELECT * FROM t18;
|
|
|
|
|
|
DELETE FROM t18;
|
|
INSERT INTO t18(a,b,c) VALUES(1,2,3);
|
|
UPDATE t18 SET c=b, b=b+1 WHERE a=1;
|
|
SELECT * FROM t18;
|
|
|
|
|
|
CREATE TABLE t19(a INT PRIMARY KEY, b, c)WITHOUT ROWID;
|
|
INSERT INTO t19(a,b,c) VALUES(1,2,3);
|
|
CREATE TRIGGER t19r3 BEFORE UPDATE ON t19 BEGIN SELECT new.b; END;
|
|
UPDATE t19 SET c=b WHERE a=1;
|
|
SELECT * FROM t19;
|
|
|
|
|
|
DELETE FROM t19;
|
|
INSERT INTO t19(a,b,c) VALUES(1,2,3);
|
|
UPDATE t19 SET c=CASE WHEN b=2 THEN b ELSE b+99 END WHERE a=1;
|
|
SELECT * FROM t19;
|
|
|
|
|
|
CREATE TABLE t20_1(x);
|
|
ATTACH ':memory:' AS aux;
|
|
CREATE TABLE aux.t20_2(y);
|
|
CREATE TABLE aux.t20_3(z);
|
|
CREATE TEMP TRIGGER r20_3 AFTER INSERT ON t20_2 BEGIN UPDATE t20_3 SET z=z+1; END;
|
|
DETACH aux;
|
|
DROP TRIGGER r20_3;
|
|
|
|
|
|
PRAGMA recursive_triggers = true;
|
|
CREATE TABLE t0(a, b, c UNIQUE);
|
|
CREATE UNIQUE INDEX i0 ON t0(b) WHERE a;
|
|
CREATE TRIGGER tr0 AFTER DELETE ON t0 BEGIN
|
|
DELETE FROM t0;
|
|
END;
|
|
INSERT INTO t0(a,b,c) VALUES(0,0,9),(1,1,1);
|
|
REPLACE INTO t0(a,b,c) VALUES(2,0,9);
|
|
SELECT * FROM t0;
|
|
|
|
|
|
CREATE TABLE t1(
|
|
a INTEGER PRIMARY KEY,
|
|
b DOUBLE
|
|
);
|
|
CREATE TRIGGER x AFTER UPDATE ON t1 BEGIN
|
|
SELECT sum(b)OVER(ORDER BY (SELECT b FROM t1 AS x
|
|
WHERE b IN (t1.a,127,t1.b)
|
|
GROUP BY b))
|
|
FROM t1
|
|
GROUP BY a;
|
|
END;
|
|
CREATE TEMP TRIGGER x BEFORE INSERT ON t1 BEGIN
|
|
UPDATE t1
|
|
SET b=randomblob(10)
|
|
WHERE b >= 'E'
|
|
AND a < (SELECT a FROM t1 WHERE a<22 GROUP BY b);
|
|
END;
|
|
INSERT INTO t1(b) VALUES('Y'),('X'),('Z');
|
|
SELECT a, CASE WHEN typeof(b)='text' THEN quote(b) ELSE '<blob>' END, '|' FROM t1;
|
|
|