title: Unexpected row count when using PostgreSQL trigger and hibernate
body: <p>I have an application which uses Hibernate and I have to include a trigger to copy to a history table all rows modified or deleted in a table. After including the PostgreSQL trigger the application doesn't work properly and is giving this error:</p>
  
  <blockquote>
    <p>org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException:
    Batch update returned unexpected row count from update [0]; actual row
    count: 0; expected: 1; nested exception is
    org.hibernate.StaleStateException: <strong>Batch update returned unexpected
    row count from update [0]; actual row count: 0; expected: 1</strong></p>
  </blockquote>
  
  <p>After googling for a while I've discovered that this error comes because Hibernate does a checking of the affected rows by the sql update and the returned rows aren't the expected ones because the trigger has also done an update. I've seen that this could be fixed by turning off rowcount on the trigger. But PostgreSQL doesn't seem to have an alternative for 'set nocount on'. </p>
  
  <p>How can I solve the problem in a trigger of PostgreSQL like the shown below?</p>
  
  <p>Thanks</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION my_trigger_fnc() RETURNS TRIGGER AS $my_trigger_fnc$
      DECLARE
          nowDate timestamp := now();
      BEGIN
  
          INSERT INTO historial_table (
                  id,
                  date_now,
                  id_mytable,
                  --some other fields
                  ...
                  ) 
          VALUES (              
                  nextVal('historial_table_seq'),
                  nowDate,
                  OLD.id_mytable
                  --some other fields
                  ...
                 );
          RETURN NEW;
      END;
  $my_trigger_fnc$ LANGUAGE plpgsql;
  
  CREATE TRIGGER my_trigger BEFORE UPDATE OR DELETE 
      ON my_table FOR EACH ROW 
      EXECUTE PROCEDURE my_trigger_fnc();
  </code></pre>
  
  <hr>
  
  <p>UPDATE: the tables are like this one:</p>
  
  <pre><code>CREATE TABLE historial_table(
      id integer,
      date_now timestamp NOT NULL,
      id_mytable integer NOT NULL,
      nserie character varying(255),
      idstore integer,
      idmodel integer,
      automatic boolean,
      broken boolean,
      idAlb integer,
      idInc integer,
      id_item integer,
      date_assign timestamp,
      PRIMARY KEY (id)  
  );
  
  CREATE TABLE my_table(
      id_mytable integer NOT NULL,
      nserie character varying(255),
      idstore integer,
      idmodel integer,
      automatic boolean,
      broken boolean,
      idAlb integer,
      idInc integer,
      id_item integer,
      date_assign timestamp,
      PRIMARY KEY (id_mytable)  
  );
  </code></pre>