CASE value within trigger?
(1) By anonymous on 2020-11-11 07:59:00 [link] [source]
Hello, Why does val not become 100 in for case 10 or 12 in the example below? (SQLite 3.31.1)
PRAGMA FOREIGN_KEYS=ON;
CREATE TABLE t1(
type INT INTEGER PRIMARY KEY
);
CREATE TABLE t2(
id INTEGER PRIMARY KEY AUTOINCREMENT,
val INT,
t1_type INT,
FOREIGN KEY (t1_type) REFERENCES t1(type)
);
CREATE TRIGGER t1_trigger
AFTER INSERT ON t1
WHEN new.type IN (10, 11, 12)
BEGIN
INSERT INTO t2 (t1_type, val)
VALUES(new.type, CASE new.type WHEN (10 OR 12) THEN 100 WHEN 11 THEN 200 END);
END;
INSERT INTO t1 VALUES (10); INSERT INTO t1 VALUES (11); INSERT INTO t1 VALUES (12);
SELECT * FROM t2; id|val|t1_type 1||10 2|200|11 3||12
(2) By Keith Medcalf (kmedcalf) on 2020-11-11 08:38:37 in reply to 1 [source]
The result of (10 or 12) is 1 (because one or the other is non-zero (True) so the result of the boolean OR is 1 (non-zero / True))
sqlite> select (10 or 12);
┌────────────┐
│ (10 or 12) │
├────────────┤
│ 1 │
└────────────┘
sqlite> CREATE TABLE t1(
...> type INT INTEGER PRIMARY KEY
...> );
sqlite> CREATE TABLE t2(
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> val INT,
...> t1_type INT,
...> FOREIGN KEY (t1_type) REFERENCES t1(type)
...> );
sqlite> CREATE TRIGGER t1_trigger
...> AFTER INSERT ON t1
...> WHEN new.type IN (1,10, 11, 12)
...> BEGIN
...> INSERT INTO t2 (t1_type, val)
...> VALUES(new.type, CASE new.type WHEN (10 OR 12) THEN 100 WHEN 11 THEN 200 END);
...> END;
sqlite>
sqlite> INSERT INTO t1 VALUES (10);
sqlite> INSERT INTO t1 VALUES (11);
sqlite> INSERT INTO t1 VALUES (12);
sqlite> INSERT INTO t1 VALUES (1);
sqlite>
sqlite> SELECT * FROM t2;
┌────┬─────┬─────────┐
│ id │ val │ t1_type │
├────┼─────┼─────────┤
│ 1 │ │ 10 │
│ 2 │ 200 │ 11 │
│ 3 │ │ 12 │
│ 4 │ 100 │ 1 │
└────┴─────┴─────────┘
You probably mean:
CASE new.type WHEN 10 THEN 100 WHEN 12 THEN 100 WHEN 11 THEN 200 END
CASE x WHEN y THEN z
is syntactic sugar for CASE WHEN x = y THEN z
and just permits you not to have to type the x =
in each WHEN clause repetitively.