Invariant Properties

public class AuditTrigger {

    public static void auditFoobar(TriggerData td) throws SQLException {

        Connection conn = DriverManager
                .getConnection("jdbc:default:connection");
        PreparedStatement ps = conn
                .prepareStatement("insert into javatest.foobar_audit(what, whenn, data) values (?, ?, ?::xml)");

        if (td.isFiredByInsert()) {
            ps.setString(1, "INSERT");
        } else if (td.isFiredByUpdate()) {
            ps.setString(1, "UPDATE");
        } else if (td.isFiredByDelete()) {
            ps.setString(1, "DELETE");
        }
        ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));

        ResultSet rs = td.getNew();
        if (rs != null) {
            ps.setString(3, toXml(rs));
        } else {
            ps.setNull(3, Types.VARCHAR);
        }

        ps.execute();
        ps.close();
    }

    // simple marshaler. We could use jaxb or similar library
    static String toXml(ResultSet rs) throws SQLException {
        String foo = rs.getString(1);
        if (rs.wasNull()) {
            foo = "";
        }
        String bar = rs.getString(2);
        if (rs.wasNull()) {
            bar = "";
        }
        return String.format("<my-class><foo>%s</foo><bar>%s</bar></my-class>", foo, bar);
    }
}

  CREATE TABLE javatest.foobar (
       foo   varchar(10),
       bar   varchar(10)
  );

  CREATE TABLE javatest.foobar_audit (
       what  varchar(10) not null,
       whenn timestamp not null,
       data  xml
  );

  CREATE FUNCTION javatest.audit_foobar()
      RETURNS trigger
      AS 'sandbox.AuditTrigger.auditFoobar'
      LANGUAGE 'java';

  CREATE TRIGGER foobar_audit
      AFTER INSERT OR UPDATE OR DELETE ON javatest.foobar
      FOR EACH ROW
      EXECUTE PROCEDURE javatest.audit_foobar();

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root folder of this project or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The SQL 2003 spec. does not stipulate a standard way of mapping
 * triggers to functions. The PLJava mapping use this interface. All
 * functions that are intended to be triggers must be public, static,
 * return void, and take a <code>TriggerData</code> as their argument.
 *
 * @author Thomas Hallgren
 */
public interface TriggerData
{
	/**
	 * Returns the ResultSet that represents the new row. This ResultSet will
	 * be null for delete triggers and for triggers that was fired for
	 * statement.
The returned set will be updateable and positioned on a
	 * valid row. When the trigger call returns, the trigger manager will see
	 * the changes that has been made to this row and construct a new tuple
	 * which will become the new or updated row.
	 *
	 * @return An updateable <code>ResultSet</code> containing one row or
	 *         <code>null</code>.
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	ResultSet getNew() throws SQLException;

	/**
	 * Returns the ResultSet that represents the old row. This ResultSet will
	 * be null for insert triggers and for triggers that was fired for
	 * statement.
The returned set will be read-only and positioned on a
	 * valid row.
	 *
	 * @return A read-only <code>ResultSet</code> containing one row or
	 *         <code>null</code>.
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	ResultSet getOld() throws SQLException;

	/**
	 * Returns the arguments for this trigger (as declared in the <code>CREATE TRIGGER</code>
	 * statement. If the trigger has no arguments, this method will return an
	 * array with size 0.
	 *
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	String[] getArguments() throws SQLException;

	/**
	 * Returns the name of the trigger (as declared in the <code>CREATE TRIGGER</code>
	 * statement).
	 *
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	String getName() throws SQLException;

	/**
	 * Returns the name of the table for which this trigger was created (as
	 * declared in the <code>CREATE TRIGGER</code statement). * * @throws SQLException * if the contained native buffer has gone stale. */ String getTableName() throws SQLException; /** * Returns the name of the schema of the table for which this trigger was created (as * declared in the <code>CREATE TRIGGER</code statement). * * @throws SQLException * if the contained native buffer has gone stale. */ String getSchemaName() throws SQLException; /** * Returns <code>true</code> if the trigger was fired after the statement * or row action that it is associated with. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredAfter() throws SQLException; /** * Returns <code>true</code> if the trigger was fired before the * statement or row action that it is associated with. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredBefore() throws SQLException; /** * Returns <code>true</code> if this trigger is fired once for each row * (as opposed to once for the entire statement). * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredForEachRow() throws SQLException; /** * Returns <code>true</code> if this trigger is fired once for the entire * statement (as opposed to once for each row). * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredForStatement() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by a <code>DELETE</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByDelete() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by an <code>INSERT</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByInsert() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by an <code>UPDATE</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByUpdate() throws SQLException; } </code></code>

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root folder of this project or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.SQLException;

/**
 * An exception specially suited to be thrown from within a method
 * designated to be a trigger function. The message generated by
 * this exception will contain information on what trigger and
 * what relation it was that caused the exception
 *
 * @author Thomas Hallgren
 */
public class TriggerException extends SQLException
{
    private static final long serialVersionUID = 5543711707414329116L;

    private static boolean s_recursionLock = false;

    public static final String TRIGGER_ACTION_EXCEPTION = "09000";

    private static final String makeMessage(TriggerData td, String message)
    {
        StringBuffer bld = new StringBuffer();
        bld.append("In Trigger ");
        if(!s_recursionLock)
        {
            s_recursionLock = true;
            try
            {
                bld.append(td.getName());
                bld.append(" on relation ");
                bld.append(td.getTableName());
            }
            catch(SQLException e)
            {
                bld.append("(exception while generating exception message)");
            }
            finally
            {
                s_recursionLock = false;
            }
        }
        if(message != null)
        {
            bld.append(": ");
            bld.append(message);
        }
        return bld.toString();
    }

    /**
     * Create an exception based on the <code>TriggerData</code> that was
     * passed to the trigger method.
     * @param td The <code>TriggerData</code> that was passed to the trigger
     * method.
     */
    public TriggerException(TriggerData td)
    {
        super(makeMessage(td, null), TRIGGER_ACTION_EXCEPTION);
    }

    /**
     * Create an exception based on the <code>TriggerData</code> that was
     * passed to the trigger method and an additional message.
     * @param td The <code>TriggerData</code> that was passed to the trigger
     * method.
     * @param reason An additional message with info about the exception.
     */
    public TriggerException(TriggerData td, String reason)
    {
        super(makeMessage(td, reason), TRIGGER_ACTION_EXCEPTION);
    }
}