SQLite Forum

CASE value within trigger?
Login

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.