Well the try/catch block and stacktrace printing is mostly for debugging
so maybe that could be removed so we don't clutter the console.
- rami
7.5.2010 15:32, Rami Ojares kirjoitti:
Here is AbstractTrigger that implements the idea I have been talking
about.
The ability to access old and new rows using their names and type info.
Maybe this could be included among the Trigger samples?
I am using this so it should work.
Let me know what you think.
- rami
package org.h2.samples;
import java.sql.*;
import org.h2.api.*;
import org.h2.tools.*;
/**
Converts old and new rows to ResultSet that contains column name
and type information.
The new and old row ResultSet has only one row and cursor is
positioned on that row.
*/
public abstract class AbstractTrigger implements Trigger {
String schemaName;
String triggerName;
String tableName;
boolean before;
int type;
public void init(Connection conn, String schemaName, String
triggerName, String tableName, boolean before, int type) {
this.schemaName = schemaName;
this.triggerName = triggerName;
this.tableName = tableName;
this.before = before;
this.type = type;
}
public void fire(Connection conn, Object[] oldRow, Object[]
newRow) throws SQLException {
try {
SimpleResultSet OLD = oldRow != null ? new
SimpleResultSet() : null;
SimpleResultSet NEW = newRow != null ? new
SimpleResultSet() : null;
ResultSet colDefs = conn.getMetaData().getColumns(null,
this.schemaName, this.tableName, null);
while(colDefs.next()) {
if (OLD != null) {
OLD.addColumn(
colDefs.getString("COLUMN_NAME"),
colDefs.getInt("DATA_TYPE"),
colDefs.getInt("COLUMN_SIZE"),
colDefs.getInt("DECIMAL_DIGITS")
);
}
if (NEW != null) {
NEW.addColumn(
colDefs.getString("COLUMN_NAME"),
colDefs.getInt("DATA_TYPE"),
colDefs.getInt("COLUMN_SIZE"),
colDefs.getInt("DECIMAL_DIGITS")
);
}
}
if (OLD != null) {
OLD.addRow(oldRow);
OLD.next();
}
if (NEW != null) {
NEW.addRow(newRow);
NEW.next();
}
fire(conn, OLD, NEW);
} catch(Throwable t) {
t.printStackTrace();
if (t instanceof SQLException) throw (SQLException) t;
}
}
public abstract void fire(Connection conn, ResultSet oldRow,
ResultSet newRow) throws SQLException;
public void remove() throws SQLException {
// noop
}
public void close() throws SQLException {
// noop
}
}
7.5.2010 2:13, Rami Ojares kirjoitti:
One more:
Indexing is sometimes 1 based and sometimes 0 based.
It's easy to get this wrong.
- rami
On 7.5.2010 0:49, Rami Ojares wrote:
Further reasons why index based column referral is inferior to name
based referral:
- If I add a new column somewhere in between the columns (say in the
beginning) I have to rewrite my triggers that are position based
This does not happen when referring to a column by name
- If I change the name of a column I can always do a search and
replace in my source code (which is much easier)
- rami
On 5.5.2010 16:18, Rami Ojares wrote:
Ok, tested it and it works so that the array of old row
1) Always contains all columns belonging to that table
2) The columns are in the order they are defined for the table
So all is quite ok.
My only (mostly aesthetic) criticism is that the columns of a table
should be a set (and thus have no ordering).
But since SQL violated that principle then I quess I can live with
that.
Maybe this remark could be added to trigger documentation?
- rami
5.5.2010 0:09, Rami Ojares kirjoitti:
Hi,
In trigger interface the fire method is defined as
void fire
<http://www.h2database.com/javadoc/org/h2/api/Trigger.html#fire_Connection_Object-_Object->(Connection
conn, Object[] oldRow, Object[] newRow) throws SQLException
Does the old/new row object array contain all the columns of the
row or just the ones mentioned in the insert/update statement?
Is there any way to know in what order those values are?
I mean if I do two two separate updates
UPDATE TBL SET COL1 = X, COL2 = Y
and
UPDATE TBL SET COL2 = Y, COL1 = X
Then are those values in the array in the order used in the statement.
If so, how am I able to know what value is which?
I think it would be great if instead of an Object Array old and
new rows could be represented as Map<String, Object>
that would map column names to row's values.
And also that the map would contain all the columns of the table.
(Maybe it already does, haven't tested that yet.
- Rami Ojares
--
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.
