SQLite

# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test trigger1-1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}

execsql {
    CREATE TABLE t1(a);
}
execsql {
	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
	  INSERT INTO t1 values(1);
 	END;
}
do_test trigger1-1.2 {
    catchsql {
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
 	END
     }
} {1 {trigger tr1 already exists}}

do_test trigger1-1.3 {
    catchsql {
	BEGIN;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
        ROLLBACK;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
    }
} {0 {}}

do_test trigger1-1.4 {
    catchsql {
	DROP TRIGGER tr1;
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
	END
    }
} {0 {}}

do_test trigger1-1.5 {
    execsql {
	BEGIN;
	DROP TRIGGER tr2;
	ROLLBACK;
	DROP TRIGGER tr2;
    }
} {}

do_test trigger1-1.6 {
    catchsql {
	DROP TRIGGER biggles;
    }
} {1 {no such trigger: biggles}}

do_test trigger1-1.7 {
    catchsql {
	DROP TABLE t1;
	DROP TRIGGER tr1;
    }
} {1 {no such trigger: tr1}}

execsql {
  CREATE TEMP TABLE temp_table(a);
}
do_test trigger1-1.8 {
  execsql {
	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
	    SELECT * from sqlite_master;
	END;
	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
  } 
} {0}

do_test trigger1-1.9 {
  catchsql {
    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
       SELECT * FROM sqlite_master;
    END;
  }
} {1 {cannot create trigger on system table: sqlite_master}}

# Check to make sure that a DELETE statement within the body of
# a trigger does not mess up the DELETE that caused the trigger to
# run in the first place.
#
do_test trigger1-1.10 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after delete on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
    delete from t1 where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {2 b 4 d}
do_test trigger1-1.11 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after update on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
    update t1 set b='x-' || b where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {1 x-a 2 b 4 d}

# Ensure that we cannot create INSTEAD OF triggers on tables
do_test trigger1-1.12 {
  catchsql {
    create table t1(a,b);
    create trigger t1t instead of update on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create INSTEAD OF trigger on table: t1}}
# Ensure that we cannot create BEFORE triggers on views
do_test trigger1-1.13 {
  catchsql {
    create view v1 as select * from t1;
    create trigger v1t before update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create BEFORE trigger on view: v1}}
# Ensure that we cannot create AFTER triggers on views
do_test trigger1-1.14 {
  catchsql {
    create table t1(a,b);
    create view v1 as select * from t1;
    create trigger v1t AFTER update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create AFTER trigger on view: v1}}

finish_test

#
# Regression testing of FOR EACH ROW table triggers
#
# 1. Trigger execution order tests. 
# These tests ensure that BEFORE and AFTER triggers are fired at the correct
# times relative to each other and the triggering statement. 
#
# trigger2-1.1.*: ON UPDATE trigger execution model.
# trigger2-1.2.*: DELETE trigger execution model.
# trigger2-1.3.*: INSERT trigger execution model.
#
# 2. Trigger program execution tests.
# These tests ensure that trigger programs execute correctly (ie. that a
# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
# statements, and combinations thereof).
#
# 3. Selective trigger execution 
# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
# with WHEN clauses) are fired only fired when they are supposed to be.
#
# trigger2-3.1: UPDATE OF triggers
# trigger2-3.2: WHEN clause
#
# 4. Cascaded trigger execution 
# Tests that trigger-programs may cause other triggers to fire. Also that a 
# trigger-program is never executed recursively.
# 
# trigger2-4.1: Trivial cascading trigger
# trigger2-4.2: Trivial recursive trigger handling 
#
# 5. Count changes behaviour.
# Verify that rows altered by triggers are not included in the return value
# of the "count changes" interface.
#
# 6. ON CONFLICT clause handling
# trigger2-6.1[a-f]: INSERT statements
# trigger2-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

      INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trigger2-1.1.$ii {
    execsql {
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }
  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \

      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trigger2-1.2.$ii {
    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }
  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \

      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  0, 0,
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }
  do_test trigger2-1.3.$ii {
    execsql {

      CREATE TABLE other_tbl(a, b);
      INSERT INTO other_tbl VALUES(1, 2);
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);

# Check execution of BEFORE programs:

    set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
    execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trigger2-2-$ii-before "execsql {$statement $query}" $before_data

    execsql "DROP TRIGGER the_trigger;"
    execsql "DELETE FROM tbl; DELETE FROM log;"

# Check execution of AFTER programs
    set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";

    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trigger2-2-$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"
  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 3.

# trigger2-3.1: UPDATE OF triggers
execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
  INSERT INTO tbl VALUES (0, 0, 0, 0);
  INSERT INTO tbl VALUES (1, 0, 0, 0);
  CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
    BEGIN
      UPDATE log SET a = a + 1;
    END;
}
do_test trigger2-3.1 {
  execsql {
    UPDATE tbl SET b = 1, c = 10; -- 2
    UPDATE tbl SET b = 10; -- 0
    UPDATE tbl SET d = 4 WHERE a = 0; --1
    UPDATE tbl SET a = 4, b = 10; --0
    SELECT * FROM log;
  }
} {3}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# trigger2-3.2: WHEN clause
set when_triggers [ list \
             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
             {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]

execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
}

foreach trig $when_triggers {
  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
}

do_test trigger2-3.2 {
  execsql { 

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
    SELECT * FROM log;
    UPDATE log SET a = 0;

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0

    INSERT INTO tblB values(new.a, new.b);
  END;

  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
    INSERT INTO tblC values(new.a, new.b);
  END;
}
do_test trigger2-4.1 {
  execsql {
    INSERT INTO tblA values(1, 2);
    SELECT * FROM tblA;
    SELECT * FROM tblB;
    SELECT * FROM tblC;
  }
} {1 2 1 2 1 2}

execsql {
  CREATE TABLE tbl(a, b, c);
  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
    BEGIN
      INSERT INTO tbl VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger2-4.2 {
  execsql {
    INSERT INTO tbl VALUES (1, 2, 3);
    select * from tbl;
  }
} {1 2 3 1 2 3}
execsql {
  DROP TABLE tbl;

      INSERT INTO tbl VALUES (1, 2, 3);
      INSERT INTO tbl VALUES (2, 2, 3);
      UPDATE tbl set b = 10 WHERE a = 1;
      DELETE FROM tbl WHERE a = 1;
      DELETE FROM tbl;
    END;
}
do_test trigger2-5 {
  execsql {
    INSERT INTO tbl VALUES(100, 200, 300);
  }
  db changes
} {1}
execsql {
  DROP TABLE tbl;
}

# Handling of ON CONFLICT by INSERT statements inside triggers
execsql {
  CREATE TABLE tbl (a primary key, b, c);
  CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
    INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
  END;
}
do_test trigger2-6.1a {
  execsql {
    BEGIN;
    INSERT INTO tbl values (1, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trigger2-6.1f {
  execsql {
    INSERT OR REPLACE INTO tbl values (2, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trigger2-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}


# Handling of ON CONFLICT by UPDATE statements inside triggers
execsql {
  INSERT INTO tbl values (4, 2, 3);
  INSERT INTO tbl values (6, 3, 4);
  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
    UPDATE OR IGNORE tbl SET a = new.a, c = 10;
  END;
}
do_test trigger2-6.2a {
  execsql {
    BEGIN;
    UPDATE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trigger2-6.2f {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
execsql {
  INSERT INTO tbl VALUES (2, 3, 4);
}
do_test trigger2-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;
}

# 7. Triggers on views
do_test trigger2-7.1 {
  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);

   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {}

do_test trigger2-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \

    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 
	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort') 
	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail') 
	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
    END;
}
# ABORT
do_test trigger3-1.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}

do_test trigger3-1.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# FAIL
do_test trigger3-2.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
do_test trigger3-2.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
do_test trigger3-3.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.2 {
    execsql {
	SELECT * FROM tbl;
    }
} {}
# IGNORE
do_test trigger3-4.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (4, 5, 6);
    }
} {0 {}}
do_test trigger3-4.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
execsql {DROP TABLE tbl;}
execsql {CREATE TABLE tbl (a, b, c);}
execsql {INSERT INTO tbl VALUES(1, 2, 3);}
execsql {INSERT INTO tbl VALUES(4, 5, 6);}
execsql {
    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;
}
do_test trigger3-5.1 {
    execsql {
	UPDATE tbl SET c = 10;
	SELECT * FROM tbl;
    }
} {1 2 3 4 5 10}
do_test trigger3-5.2 {
    execsql {
	DELETE FROM tbl;
	SELECT * FROM tbl;
    }
} {1 2 3}

# Check that RAISE(IGNORE) works correctly for nested triggers:
execsql {CREATE TABLE tbl2(a, b, c)}
execsql {
    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
	UPDATE tbl SET c = 10;
        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger3-6 {
    execsql {
	INSERT INTO tbl2 VALUES (1, 2, 3);
	SELECT * FROM tbl2;
	SELECT * FROM tbl;
    }
} {1 2 3 1 2 3 1 2 3}

# Check that things also work for view-triggers
execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
execsql {
    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
	            WHEN (new.a = 2) THEN RAISE(IGNORE) 
	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
    END;
}

do_test trigger3-7.1 {
    catchsql {
	INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
do_test trigger3-7.2 {
    catchsql {
	INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
	INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test