mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
96 lines
2.2 KiB
Plaintext
96 lines
2.2 KiB
Plaintext
|
|
CREATE TABLE map(k, v);
|
|
INSERT INTO map VALUES(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
|
|
|
|
CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET c = v FROM map WHERE k=new.a AND a=new.a;
|
|
END;
|
|
|
|
|
|
INSERT INTO t1(a) VALUES(1);
|
|
|
|
|
|
SELECT a, c FROM t1 ORDER BY a;
|
|
|
|
|
|
INSERT INTO t1(a) VALUES(2), (3), (4), (5);
|
|
SELECT a, c FROM t1 ORDER BY a;
|
|
|
|
|
|
ATTACH 'test.db2' AS aux;
|
|
CREATE TABLE aux.t3(x, y);
|
|
INSERT INTO aux.t3 VALUES('x', 'y');
|
|
|
|
|
|
CREATE TEMP TRIGGER tr2 AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET b = y FROM aux.t3 WHERE a=new.a;
|
|
END;
|
|
INSERT INTO t1(a) VALUES(10), (20);
|
|
SELECT * FROM t1;
|
|
|
|
|
|
CREATE TABLE link(f, t);
|
|
INSERT INTO link VALUES(5, 2), (20, 10), (2, 1);
|
|
CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN
|
|
UPDATE t1 SET b=coalesce(old.b,old.c) FROM main.link WHERE a=t AND old.a=f;
|
|
END;
|
|
DELETE FROM t1 WHERE a=2;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
CREATE TABLE mmm(x, y);
|
|
INSERT INTO mmm VALUES(1, 'one');
|
|
INSERT INTO mmm VALUES(2, 'two');
|
|
INSERT INTO mmm VALUES(3, 'three');
|
|
|
|
ATTACH 'test.db2' AS aux;
|
|
CREATE TABLE aux.t1(a, b);
|
|
CREATE TABLE aux.mmm(x, y);
|
|
INSERT INTO aux.mmm VALUES(1, 'ONE');
|
|
INSERT INTO aux.mmm VALUES(2, 'TWO');
|
|
INSERT INTO aux.mmm VALUES(3, 'THREE');
|
|
|
|
CREATE TRIGGER aux.ttt AFTER INSERT ON t1 BEGIN
|
|
UPDATE t1 SET b=y FROM mmm WHERE x=new.a AND a=new.a;
|
|
END;
|
|
|
|
INSERT INTO t1(a) VALUES (2);
|
|
SELECT * FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(k, a, b);
|
|
INSERT INTO t1 VALUES('a', 1, 'one');
|
|
INSERT INTO t1 VALUES('b', 2, 'two');
|
|
INSERT INTO t1 VALUES('c', 3, 'three');
|
|
INSERT INTO t1 VALUES('d', 4, 'four');
|
|
|
|
CREATE TABLE log(x);
|
|
CREATE VIEW v1 AS SELECT k, a, b AS __hidden__b FROM t1;
|
|
CREATE TRIGGER tr1 INSTEAD OF UPDATE ON v1 BEGIN
|
|
INSERT INTO log VALUES(
|
|
'('||old.a||','||old.__hidden__b||')->('||new.a||','||new.__hidden__b||')'
|
|
);
|
|
END;
|
|
|
|
|
|
SELECT * FROM v1
|
|
|
|
|
|
SELECT * FROM v1
|
|
|
|
|
|
UPDATE v1 SET a='xyz' WHERE k IN ('a', 'c');
|
|
SELECT * FROM log;
|
|
DELETE FROM log;
|
|
|
|
|
|
CREATE TABLE map(k, v);
|
|
INSERT INTO map VALUES('b', 'twelve');
|
|
INSERT INTO map VALUES('d', 'fourteen');
|
|
UPDATE v1 SET a=map.v FROM map WHERE v1.k=map.k;
|
|
SELECT * FROM log;
|
|
DELETE FROM log;
|
|
|