trigger1.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    }
  } {1 2 3 4 3 4}
  do_test trigger1-3.9 {
    db close
    sqlite3 db test.db
    execsql {
      INSERT INTO t1 VALUES(5,6);
      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    }
  } {1 2 3 4 5 6 3 4}
  } ;# ifcapable compound
  ifcapable !compound {
  do_test trigger1-3.8 {
    execsql {
      INSERT INTO t1 VALUES(3,4);
      SELECT * FROM t1; 
      SELECT * FROM t2;
    }
  } {1 2 3 4 3 4}
  do_test trigger1-3.9 {
    db close
    sqlite3 db test.db
    execsql {
      INSERT INTO t1 VALUES(5,6);
      SELECT * FROM t1;
      SELECT * FROM t2;
    }
  } {1 2 3 4 5 6 3 4}
  } ;# ifcapable !compound

  do_test trigger1-4.1 {
    execsql {
      CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
        INSERT INTO t2 VALUES(NEW.a,NEW.b);
      END;
      INSERT INTO t1 VALUES(7,8);
      SELECT * FROM t2;
    }
  } {3 4 7 8}
  do_test trigger1-4.2 {
    sqlite3 db2 test.db
    execsql {
      INSERT INTO t1 VALUES(9,10);
    } db2;
    db2 close
    execsql {
      SELECT * FROM t2;
    }
  } {3 4 7 8}
  do_test trigger1-4.3 {
    execsql {
      DROP TABLE t1;
      SELECT * FROM t2;
    };
  } {3 4 7 8}
  do_test trigger1-4.4 {
    db close
    sqlite3 db test.db
    execsql {
      SELECT * FROM t2;
    };
  } {3 4 7 8}
} else {
  execsql {
    CREATE TABLE t2(x,y);
    DROP TABLE t1;
    INSERT INTO t2 VALUES(3, 4);
    INSERT INTO t2 VALUES(7, 8);
  }
}


integrity_check trigger1-5.1

# Create a trigger with the same name as a table.  Make sure the
# trigger works.  Then drop the trigger.  Make sure the table is
# still there.
#
set view_v1 {}
ifcapable view {
  set view_v1 {view v1}
}
do_test trigger1-6.1 {
  execsql {SELECT type, name FROM sqlite_master}
} [concat $view_v1 {table t2}]
do_test trigger1-6.2 {
  execsql {
    CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
      SELECT RAISE(ABORT,'deletes are not allows');
    END;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.3 {
  catchsql {DELETE FROM t2}
} {1 {deletes are not allows}}
do_test trigger1-6.4 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.5 {
  db close
  sqlite3 db test.db
  execsql {SELECT type, name FROM sqlite_master}
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.6 {
  execsql {
    DROP TRIGGER t2;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2}]
do_test trigger1-6.7 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.8 {
  db close
  sqlite3 db test.db
  execsql {SELECT * FROM t2}
} {3 4 7 8}

integrity_check trigger-7.1

# Check to make sure the name of a trigger can be quoted so that keywords
# can be used as trigger names.  Ticket #468
#
do_test trigger1-8.1 {
  execsql {
    CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.2 {
  execsql {
    DROP TRIGGER 'trigger';
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}
do_test trigger1-8.3 {
  execsql {
    CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.4 {
  execsql {
    DROP TRIGGER "trigger";
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}
do_test trigger1-8.5 {
  execsql {
    CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.6 {
  execsql {
    DROP TRIGGER [trigger];
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}

ifcapable conflict {
  # Make sure REPLACE works inside of triggers.
  #
  # There are two versions of trigger-9.1 and trigger-9.2. One that uses
  # compound SELECT statements, and another that does not.
  ifcapable compound {
    do_test trigger1-9.1 {
      execsql {
        CREATE TABLE t3(a,b);
        CREATE TABLE t4(x UNIQUE, b);
        CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
          REPLACE INTO t4 VALUES(new.a,new.b);
        END;
        INSERT INTO t3 VALUES(1,2);
        SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
      }
    } {1 2 99 99 1 2}
    do_test trigger1-9.2 {
      execsql {
        INSERT INTO t3 VALUES(1,3);
        SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
      }
    } {1 2 1 3 99 99 1 3}
  } else {
    do_test trigger1-9.1 {
      execsql {
        CREATE TABLE t3(a,b);
        CREATE TABLE t4(x UNIQUE, b);
        CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
          REPLACE INTO t4 VALUES(new.a,new.b);
        END;
        INSERT INTO t3 VALUES(1,2);
        SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
      }
    } {1 2 99 99 1 2}
    do_test trigger1-9.2 {
      execsql {
        INSERT INTO t3 VALUES(1,3);
        SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
      }
    } {1 2 1 3 99 99 1 3}
  }
  execsql {
    DROP TABLE t3;
    DROP TABLE t4;
  }
}


# Ticket #764. At one stage TEMP triggers would fail to re-install when the
# schema was reloaded. The following tests ensure that TEMP triggers are
# correctly re-installed.
#
# Also verify that references within trigger programs are resolved at
# statement compile time, not trigger installation time. This means, for
# example, that you can drop and re-create tables referenced by triggers. 
ifcapable tempdb {
  do_test trigger1-10.0 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
    }
  } {}
  do_test trigger1-10.1 {
    execsql {
      CREATE TABLE main.t4(a, b, c);
      CREATE TABLE temp.t4(a, b, c);
      CREATE TABLE aux.t4(a, b, c);
      CREATE TABLE insert_log(db, a, b, c);
    }
  } {}
  do_test trigger1-10.2 {
    execsql {
      CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 
        INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
      END;
      CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 
        INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
      END;
      CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 
        INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
      END;
    }
  } {}
  do_test trigger1-10.3 {
    execsql {
      INSERT INTO main.t4 VALUES(1, 2, 3);
      INSERT INTO temp.t4 VALUES(4, 5, 6);
      INSERT INTO aux.t4  VALUES(7, 8, 9);
    }
  } {}
  do_test trigger1-10.4 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 1 2 3 temp 4 5 6 aux 7 8 9}
  do_test trigger1-10.5 {
    execsql {
      BEGIN;
      INSERT INTO main.t4 VALUES(1, 2, 3);
      INSERT INTO temp.t4 VALUES(4, 5, 6);
      INSERT INTO aux.t4  VALUES(7, 8, 9);
      ROLLBACK;
    }
  } {}
  do_test trigger1-10.6 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 1 2 3 temp 4 5 6 aux 7 8 9}
  do_test trigger1-10.7 {
    execsql {
      DELETE FROM insert_log;
      INSERT INTO main.t4 VALUES(11, 12, 13);
      INSERT INTO temp.t4 VALUES(14, 15, 16);
      INSERT INTO aux.t4  VALUES(17, 18, 19);
    }
  } {}
  do_test trigger1-10.8 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 11 12 13 temp 14 15 16 aux 17 18 19}
  do_test trigger1-10.8 {
  # Drop and re-create the insert_log table in a different database. Note
  # that we can change the column names because the trigger programs don't
  # use them explicitly.
    execsql {
      DROP TABLE insert_log;
      CREATE TABLE aux.insert_log(db, d, e, f);
    }
  } {}
  do_test trigger1-10.10 {
    execsql {
      INSERT INTO main.t4 VALUES(21, 22, 23);
      INSERT INTO temp.t4 VALUES(24, 25, 26);
      INSERT INTO aux.t4  VALUES(27, 28, 29);
    }
  } {}
  do_test trigger1-10.11 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 21 22 23 temp 24 25 26 aux 27 28 29}
}

do_test trigger1-11.1 {
  catchsql {SELECT raise(abort,'message');}
} {1 {RAISE() may only be used within a trigger-program}}


finish_test