Examples with Trigger org.h2.api.Trigger used on opensource projects

Example 26 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testTriggerBeforeSelect.

private void testTriggerBeforeSelect() throws SQLException {
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("drop table if exists meta_tables");
    stat.execute("create table meta_tables(name varchar)");
    stat.execute("create trigger meta_tables_select " + "before select on meta_tables call \"" + TestSelect.class.getName() + "\"");
    ResultSet rs;
    rs = stat.executeQuery("select * from meta_tables");
    assertTrue(rs.next());
    assertFalse(rs.next());
    stat.execute("create table test(id int)");
    rs = stat.executeQuery("select * from meta_tables");
    assertTrue(rs.next());
    assertTrue(rs.next());
    assertFalse(rs.next());
    conn.close();
    if (!config.memory) {
        conn = getConnection("trigger");
        stat = conn.createStatement();
        stat.execute("create table test2(id int)");
        rs = stat.executeQuery("select * from meta_tables");
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertFalse(rs.next());
        conn.close();
    }
}

Example 27 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testTriggerDeadlock.

private void testTriggerDeadlock() throws Exception {
    final Connection conn, conn2;
    final Statement stat, stat2;
    conn = getConnection("trigger");
    conn2 = getConnection("trigger");
    stat = conn.createStatement();
    stat2 = conn2.createStatement();
    stat.execute("create table test(id int) as select 1");
    stat.execute("create table test2(id int) as select 1");
    stat.execute("create trigger test_u before update on test2 " + "for each row call \"" + DeleteTrigger.class.getName() + "\"");
    conn.setAutoCommit(false);
    conn2.setAutoCommit(false);
    stat2.execute("update test set id = 2");
    Task task = new Task() {

        @Override
        public void call() throws Exception {
            Thread.sleep(300);
            stat2.execute("update test2 set id = 4");
        }
    };
    task.execute();
    Thread.sleep(100);
    try {
        stat.execute("update test2 set id = 3");
        task.get();
    } catch (SQLException e) {
        assertEquals(ErrorCode.LOCK_TIMEOUT_1, e.getErrorCode());
    }
    conn2.rollback();
    conn.rollback();
    stat.execute("drop table test");
    stat.execute("drop table test2");
    conn.close();
    conn2.close();
}

Example 28 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testConstraints.

private void testConstraints() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("create table test(id int primary key, parent int)");
    stat.execute("alter table test add constraint test_parent_id " + "foreign key(parent) references test (id) on delete cascade");
    stat.execute("insert into test select x, x/2 from system_range(0, 100)");
    stat.execute("delete from test");
    assertSingleValue(stat, "select count(*) from test", 0);
    stat.execute("drop table test");
    conn.close();
}

Example 29 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testMultiPartForeignKeys.

/**
 * Regression test: we had a bug where the AlterTableAddConstraint class
 * used to sometimes pick the wrong unique index for a foreign key.
 */
private void testMultiPartForeignKeys() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST1");
    stat.execute("DROP TABLE IF EXISTS TEST2");
    stat.execute("create table test1(id int primary key, col1 int)");
    stat.execute("alter table test1 add constraint unique_test1 " + "unique (id,col1)");
    stat.execute("create table test2(id int primary key, col1 int)");
    stat.execute("alter table test2 add constraint fk_test2 " + "foreign key(id,col1) references test1 (id,col1)");
    stat.execute("insert into test1 values (1,1)");
    stat.execute("insert into test1 values (2,2)");
    stat.execute("insert into test1 values (3,3)");
    stat.execute("insert into test2 values (1,1)");
    assertThrows(23506, stat).execute("insert into test2 values (2,1)");
    assertSingleValue(stat, "select count(*) from test1", 3);
    assertSingleValue(stat, "select count(*) from test2", 1);
    stat.execute("drop table test1");
    stat.execute("drop table test2");
    conn.close();
}

Example 30 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testTrigger.

private void testTrigger(final String sourceLang) throws SQLException {
    final String callSeq = "call seq.nextval";
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("create sequence seq");
    stat.execute("create table test(id int primary key)");
    assertSingleValue(stat, callSeq, 1);
    conn.setAutoCommit(false);
    Trigger t = new org.h2.test.db.TestTriggersConstraints.TestTriggerAlterTable();
    t.close();
    if ("java".equals(sourceLang)) {
        String triggerClassName = this.getClass().getName() + "." + TestTriggerAlterTable.class.getSimpleName();
        stat.execute("create trigger test_upd before insert on test " + "as $$org.h2.api.Trigger create() " + "{ return new " + triggerClassName + "(); } $$");
    } else if ("javascript".equals(sourceLang)) {
        String triggerClassName = this.getClass().getName() + "." + TestTriggerAlterTable.class.getSimpleName();
        final String body = "//javascript\n" + "new Packages." + triggerClassName + "();";
        stat.execute("create trigger test_upd before insert on test as $$" + body + " $$");
    } else {
        stat.execute("create trigger test_upd before insert on test call \"" + TestTriggerAlterTable.class.getName() + "\"");
    }
    stat.execute("insert into test values(1)");
    assertSingleValue(stat, callSeq, 3);
    stat.execute("alter table test add column name varchar");
    assertSingleValue(stat, callSeq, 4);
    stat.execute("drop sequence seq");
    stat.execute("drop table test");
    conn.close();
}