How to avoid "tuple was already modified" error when using a BEFORE trigger in PostgreSQL?

3.2k views Asked by At

In attempting to use triggers (PostgreSQL 9.1) to prevent duplicate entries, I receive the following error:

ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.emphasized text

This error was received by executing:

update dx set cicd9='721.90', cdesc='osteoarthritis of spine, nos' where cicd9 = '721.90' and cdesc=' osteoarthritis of spine, nos';

(The attempt here is to remove the spaces in cdesc.)

If I "Return Null" from the update in the trigger, then everything gets deleted!?

How should the trigger be written to allow the calling "update dx" to work and yet prevent the result causing a duplicate key?

(Note: If the target record is being updated to a "new" value that already exists, I am trying to delete the "old" value and keep the "new" value. If I Return NULL on the update, then the old value is deleted, but there is no new value recorded??).

What am I doing wrong?

Below is the trigger I am working with:

  CREATE OR REPLACE FUNCTION dx_ins_up_before()
  RETURNS trigger AS 
  $func$

BEGIN
    IF TG_OP = 'UPDATE' THEN

        RAISE NOTICE 'doing update';

        IF EXISTS (SELECT 1 FROM dx
                WHERE (cicd9,    cdesc,    groupid,    tposted)
                = (new.cicd9, new.cdesc, new.groupid, new.tposted) ) 
        THEN
        RAISE NOTICE 'doing delete from update';

              DELETE FROM dx 
                WHERE (cicd9,    cdesc,    groupid,    tposted)
                = (OLD.cicd9, OLD.cdesc, OLD.groupid,  OLD.tposted ); 
        END IF;
        RETURN new; 
    ELSE
    -- doing insert
        RAISE NOTICE 'doing insert';

        IF EXISTS (SELECT 1 FROM dx
                WHERE (cicd9,    cdesc,    groupid,    tposted)
                = (new.cicd9, new.cdesc, new.groupid, new.tposted) ) 
        THEN
        RAISE NOTICE 'return null from insert';
            RETURN NULL;    
        END IF;
    END IF;                 
END;    

$func$  LANGUAGE plpgsql;


 CREATE TRIGGER dx_ins_up_trigger
BEFORE INSERT OR UPDATE OF cicd9,cdesc,groupid,tposted
ON dx
FOR EACH ROW
EXECUTE PROCEDURE dx_ins_up_before();

The constraint in dx is:

CONSTRAINT noduplicate_dx UNIQUE (cicd9, cdesc, groupid, tposted),

TIA

0

There are 0 answers