From: rakesh at rakeshv.org (Rakesh Vidyadharan)
Date: Wed, 2 Nov 2005 08:39:59 -0600
Subject: [Pljava-dev] PLJAVA Trigger Function more arguments
In-Reply-To: <5234B8D9C36F9E4D9B1AF01013708B6B0152C05B@txindoki.tekniker.es>
References: <5234B8D9C36F9E4D9B1AF01013708B6B0152C05B@txindoki.tekniker.es>
Message-ID: <95C1D62B-0CF7-40B9-A378-080E088DEE4A@rakeshv.org>


On 2 Nov 2005, at 08:30, Ignacio L?zaro wrote:

>> Do you mean execute a SQL statement from within a Trigger?  That
>> should be possible using the usual JDBC techniques.  The trigger body
>> can execute any standard Java code, provided that the TriggerData can
>> supply you with the information you need to execute the associated  
>> code.
>>
>
> I am not very sure, you mean that the trigger body is inside  
> postgreSQL or inside the java class??
> do you have any example ?
> Regards
> Nacho

The following is the only time I have done a trigger in PL/Java:

   /**
    * The trigger to be invoked when deleting a <code>Group</code>
    * record.  All <code>User</code> records that are exclusively
    * associated with the <code>Group</code> specified will be
    * deleted.
    *
    * @see UserFunctions#deleteByGroup
    * @param triggerData The PostgreSQL provided interface with
    *   data pertaining to the old or new records as appropriate
    * @throws SQLException If errors are encountered while deleting
    *   the User records.  Also thrown if the trigger is fired by
    *   a <code>non-delete</code> operation on the table.
    */
   public static void deleteTrigger( TriggerData triggerData )
     throws SQLException
   {
     if( triggerData.isFiredByDelete() &&
         triggerData.isFiredForEachRow() )
     {
       ResultSet resultSet = null;
       try
       {
         resultSet = triggerData.getOld();
         resultSet.next();
         UserFunctions.deleteByGroup( resultSet.getInt( "groupId" ) );
       }
       finally
       {
         CloseResources.close( resultSet );
       }
     }
     else
     {
       throw new TriggerException( triggerData,
           "Can process only DELETE events on each row" );
     }
   }

The UserFunctions.deleteByGroup is defined as:

   /**
    * Delete all the <code>Users</code> who are solely associated with
    * the specified <code>Group</code>.
    *
    * @param groupId The primary key value of the group whose exclusive
    *   users are to be deleted.
    * @return boolean - Returns <code>false</code> to indicate that the
    *   executing the statement returns nothing.
    * @throws SQLException If errors are encountered while deleting
    *   the users.
    */
   public static final boolean deleteByGroup( int groupId )
     throws SQLException
   {
     Connection connection = null;
     PreparedStatement statement = null;

     try
     {
       connection = ConnectionFactory.getConnection();
       statement = connection.prepareStatement( deleteByGroup );
       statement.setInt( 1, groupId );
       statement.setInt( 2, groupId );
       statement.execute();
     }
     finally
     {
       CloseResources.close( statement );
       CloseResources.close( connection );
     }

     return false;
   }