On Nov 29, 2011, at 18:28 , Noel Grandin wrote:
> Implement the Trigger interface and check the oldRow vs newRow
> parameters to see which fields have changed.
This is the workaround:
public class UpdateLastModifiedTrigger extends TriggerAdapter {
public static final String COLUMN_ID = "ID";
public static final String COLUMN_LASTMODIFIED = "LASTMODIFIED";
protected String tableName;
@Override
public void init(Connection conn, String schemaName, String triggerName,
String tableName, boolean before, int type) throws SQLException {
super.init(conn, schemaName, triggerName, tableName, before, type);
this.tableName = tableName;
}
@Override
public void fire(Connection conn, ResultSet oldRow, ResultSet newRow)
throws SQLException {
if (newRow == null ) return; // This is a DELETE
if (oldRow != null) {
// This is an UPDATE
// Yes, our UPDATE statement below will fire this trigger again,
filter that!
Timestamp oldTimestamp = oldRow.getTimestamp(COLUMN_LASTMODIFIED);
Timestamp newTimestamp = newRow.getTimestamp(COLUMN_LASTMODIFIED);
if (oldTimestamp == null)
return;
if (oldTimestamp.getTime() != newTimestamp.getTime())
return;
}
PreparedStatement statement = conn.prepareStatement(
"update " + tableName +
" set " + COLUMN_LASTMODIFIED + " =
current_timestamp()" +
" where " + COLUMN_ID + " = ?"
);
// This assumes the PK column is named "ID"
Long id = newRow.getLong(COLUMN_ID);
statement.setLong(1, id);
statement.execute();
}
}
It would be so much nicer if I could return false from init(), indicating that
I don't want this trigger running more than once in the current command. You
know, the same as component.setValue(dontFireEvents) in the Swing API or really
any other event/listener API I can think of.
--
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.
