triggersample.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

            case INSERT_BEFORE :
            case INSERT_BEFORE_ROW :
            case UPDATE_BEFORE :
            case UPDATE_BEFORE_ROW :
            case DELETE_BEFORE :
            case DELETE_BEFORE_ROW : {
                return "BEFORE";
            }
            case INSERT_AFTER :
            case INSERT_AFTER_ROW :
            case UPDATE_AFTER :
            case UPDATE_AFTER_ROW :
            case DELETE_AFTER :
            case DELETE_AFTER_ROW : {
                return "AFTER";
            }
            default : {
                return "";
            }
        }
    }

    public static String getOperationSpec(int type) {

        switch (type) {

            case INSERT_AFTER :
            case INSERT_AFTER_ROW :
            case INSERT_BEFORE :
            case INSERT_BEFORE_ROW : {
                return "INSERT";
            }
            case UPDATE_AFTER :
            case UPDATE_AFTER_ROW :
            case UPDATE_BEFORE :
            case UPDATE_BEFORE_ROW : {
                return "UPDATE";
            }
            case DELETE_AFTER :
            case DELETE_AFTER_ROW :
            case DELETE_BEFORE :
            case DELETE_BEFORE_ROW : {
                return "DELETE";
            }
            default : {
                return "";
            }
        }
    }

    public static String getQueueSpec(int qs) {
        return (qs < 0) ? ""
                        : ("QUEUE " + qs);
    }

    public static String getForEachSpec(int type) {

        switch (type) {

            case INSERT_BEFORE_ROW :
            case INSERT_AFTER_ROW :
            case UPDATE_BEFORE_ROW :
            case UPDATE_AFTER_ROW :
            case DELETE_AFTER_ROW :
            case DELETE_BEFORE_ROW : {
                return "FOR EACH ROW";
            }
            default : {
                return "";
            }
        }
    }

    public static String getTriggerDDL(String trn, int typ, String tab,
                                       int qs,
                                       String impl) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("CREATE TRIGGER ");
        sb.append(trn);
        sb.append(' ');
        sb.append(getWhenSpec(typ));
        sb.append(' ');
        sb.append(getOperationSpec(typ));
        sb.append(" ON ");
        sb.append(tab);
        sb.append(' ');
        sb.append(getForEachSpec(typ));
        sb.append(' ');
        sb.append(getQueueSpec(qs));
        sb.append(" CALL \"");
        sb.append(impl);
        sb.append("\"");

        return sb.toString();
    }

    public static String getTriggerDescriptor(String trn, int typ,
            String tab) {

        StringBuffer sb = new StringBuffer();

        sb.append("TRIGGER : ");
        sb.append(trn);
        sb.append(' ');
        sb.append(getWhenSpec(typ));
        sb.append(' ');
        sb.append(getOperationSpec(typ));
        sb.append(" ON ");
        sb.append(tab);
        sb.append(' ');
        sb.append(getForEachSpec(typ));

        return sb.toString();
    }

    private static Connection getConnection() throws SQLException {

        try {
            Class.forName(drv).newInstance();

            return DriverManager.getConnection(url, usr, pwd);
        } catch (SQLException se) {
            throw se;
        } catch (Exception e) {
            throw new SQLException(e.toString());
        }
    }

    private static void createTrigger(Statement stmt, String trn,
                                      int typ) throws SQLException {
        stmt.execute(getTriggerDDL(trn, typ, tn, 0, impl));
    }

    private static void setup() throws SQLException {

        Connection conn = getConnection();
        Statement  stmt = conn.createStatement();

        stmt.execute(drop_test_table_stmt);
        stmt.execute(create_test_table_stmt);
        stmt.execute(drop_audit_table_stmt);
        stmt.execute(create_audit_table_stmt);
        createTrigger(stmt, "tib_" + tn, INSERT_BEFORE);
        createTrigger(stmt, "tibr_" + tn, INSERT_BEFORE_ROW);
        createTrigger(stmt, "tia_" + tn, INSERT_AFTER);
        createTrigger(stmt, "tiar_" + tn, INSERT_AFTER_ROW);
        createTrigger(stmt, "tub_" + tn, UPDATE_BEFORE);
        createTrigger(stmt, "tubr_" + tn, UPDATE_BEFORE_ROW);
        createTrigger(stmt, "tua_" + tn, UPDATE_AFTER);
        createTrigger(stmt, "tuar_" + tn, UPDATE_AFTER_ROW);
        createTrigger(stmt, "tdb_" + tn, DELETE_BEFORE);
        createTrigger(stmt, "tdbr_" + tn, DELETE_BEFORE_ROW);
        createTrigger(stmt, "tda_" + tn, DELETE_AFTER);
        createTrigger(stmt, "tdar_" + tn, DELETE_AFTER_ROW);
        stmt.close();
        conn.close();
    }

    private static void doSomeWork() throws SQLException {

        Connection conn = getConnection();
        Statement  stmt = conn.createStatement();

        conn.setAutoCommit(false);
        stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
        stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
        stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
        stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
        conn.commit();
        dumpTable("trig_test");
        stmt.execute("UPDATE trig_test SET value = 'all done'");
        conn.commit();
        dumpTable("trig_test");
        stmt.execute("DELETE FROM trig_test");
        conn.rollback();
        dumpTable("trig_test");

        try {
            stmt.execute("INSERT INTO trig_test VALUES(11, 'whatever')");
        } catch (SQLException se) {
            se.printStackTrace();
        }

        stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
        conn.commit();
        dumpTable("trig_test");
        stmt.close();
        conn.close();
    }

    private static void dumpTable(String tn) throws SQLException {

        Connection        conn  = getConnection();
        Statement         stmt  = conn.createStatement();
        ResultSet         rs    = stmt.executeQuery("select * from " + tn);
        ResultSetMetaData rsmd  = rs.getMetaData();
        int               count = rsmd.getColumnCount();

        out.println();
        out.println("****************************************");
        out.println("DUMP FOR TABLE: " + tn);
        out.println("****************************************");
        out.flush();

        while (rs.next()) {
            out.print("[");

            for (int i = 1; i <= count; i++) {
                out.print(rs.getString(i));

                if (i < count) {
                    out.print(" : ");
                }
            }

            out.println("]");
        }

        out.println();
        out.flush();
        rs.close();
        stmt.close();
        conn.close();
    }

    private static void runSample() throws SQLException {

        setup();
        doSomeWork();
        dumpTable("audit");
    }

    public static void main(String[] args) throws SQLException {
        runSample();
    }
}
/*
    test SQL
    CREATE CACHED TABLE trig_test (int_field     integer)
    CREATE TRIGGER ins_before BEFORE INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER ins_after  AFTER  INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER upd_before BEFORE UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER upd_after  AFTER  UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER upd_before_row BEFORE UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER upd_after_row  AFTER  UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER del_before BEFORE DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER del_after  AFTER  DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER del_before_row BEFORE DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
    CREATE TRIGGER del_after_row  AFTER  DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
    INSERT INTO trig_test VALUES (1)
    INSERT INTO trig_test VALUES (2)
    INSERT INTO trig_test VALUES (3)
    UPDATE trig_test SET int_field = int_field + 3
    DELETE FROM trig_test
 */