Thread: Question on how to conditionally commit or rollback a sql-request

Hi all,

Making an update to a db-table I want to check if it is the same data that has already been updated within nearest 0.5 minute before committing

The table contains a timestamp-field, registeredatdatetime, that I try to use in a before-update-trigger like:

/* TRIGGER: objectdescription_bu */

CREATE OR REPLACE FUNCTION objectdescription_bu() RETURNS trigger AS $$

DECLARE

  intval  INTEGER;

BEGIN

  intval = (current_timestamp - old.registereddatetime);

  IF NEW.STATUS = OLD.STATUS or

     NEW.POSITIONCODE = OLD.POSITIONCODE and

     intval > (interval '30 seconds') then

    Commit;

  ELSE

    Rollback;

  END IF;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER objectdescription_bu

  BEFORE UPDATE

  ON objectdescription

  FOR EACH ROW

  EXECUTE PROCEDURE objectdescription_bu();

When I try to do an update like:

  update objectdescription

  set status = 466

  where

      customerid = 25 AND

      objectnumber = 4;

I get the following error:

ERROR: SPI execute_plan failed executing query “Commit”:

SPI_ERROR_TRANSACTION

CONTEXT: Pl/pgSQL function “objectdescription_bu” line 9 at SQL statement

What does this mean and what should I do to be able to do the desired check

Regards
Staffan Bergbom