Hi,
in the same line of Kerry suggestion, I found a test case from an old project
that seems similar to your description, take a look above.
regards,
Dario.
package testh2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.api.Trigger;
/**
*
* @author dfassi
*/
public class Main {
public static void main(String... args) throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
Statement stat = conn.createStatement();
stat.execute("create table subjects ( "
+ "id int AUTO_INCREMENT not null primary key, "
+ "descrip varchar(50), "
+ "period_name varchar(20) not null, "
+ "year int not null )");
stat.execute("create table periods ( "
+ "name varchar(20) not null primary key, "
+ "name_en varchar(20), "
+ "month smallint, "
+ "to_month smallint )");
stat.execute("CREATE TRIGGER TRIG_INS BEFORE INSERT ON subjects FOR
EACH ROW CALL \"testh2.Main$DefinePeriod\"");
/* Simulated input from RSS */
stat.execute("INSERT INTO subjects (descrip, period_name, year )
VALUES('Seminario de Semiotica','Marzo a Julio',2009)");
stat.execute("INSERT INTO subjects (descrip, period_name, year )
VALUES('Italia. Marton laurea','Novembre',2009)");
stat.execute("INSERT INTO subjects (descrip, period_name, year )
VALUES('Start of ','April',2009)");
stat.execute("INSERT INTO subjects (descrip, period_name, year )
VALUES('Programming I','Jan',2009)");
stat.execute("INSERT INTO subjects (descrip, period_name, year )
VALUES('Programming III','Jan',2009)");
printQuery(conn, "select p.month, s.* from subjects s left outer join
periods p on (s.period_name=p.name) order by s.year,p.month ");
printQuery(conn, "select * from periods order by name");
/* on Language and periods fixation */
stat.execute("update periods set month = 3 , to_month= 7,
name_en='March to July'' where name = 'Marzo a Julio'");
stat.execute("update periods set month = 11 , name_en='November' where
name = 'Novembre'");
stat.execute("update periods set month = 4 where name = 'April'");
stat.execute("update periods set month = 1 where name = 'Jan'");
printQuery(conn, "select p.month, s.* from subjects s left outer join
periods p on (s.period_name=p.name) order by s.year,p.month ");
printQuery(conn, "select * from periods order by name");
conn.close();
}
/** Trigger class */
public static class DefinePeriod implements Trigger {
/** Fire method */
public void fire(Connection conn, Object[] oldRow, Object[] newRow)
throws SQLException {
String period_id = null, period_name = (String) newRow[2];
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select name from periods where
name ='" + period_name + "' ");
if (rs.next()) {
period_id = rs.getString(1);
}
rs.close();
if (period_id == null) {
if (stat.executeUpdate("insert into periods (name) values ('" +
period_name + "') ") != 1) {
throw new SQLException("Something wrong with " +
period_name);
}
}
stat.close();
}
public void init(Connection conn, String schemaName, String
triggerName, String tableName, boolean before, int type) {
}
public void close() {
}
public void remove() {
}
} // end trigger
public static void printQuery(Connection con, String sql) throws
SQLException {
System.out.println("\n-- Query: " + sql);
Statement stat = con.createStatement();
ResultSet rs = stat.executeQuery(sql);
while (rs.next()) {
StringBuilder s = new StringBuilder();
for (int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
Object o = rs.getObject(i);
s.append(o).append(" | ");
}
System.out.println(s.toString());
}
rs.close();
stat.close();
System.out.println("--");
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
