title: How to avoid "tuple was already modified" error when using a BEFORE trigger in PostgreSQL?
body: <p>In attempting to use triggers (PostgreSQL 9.1) to prevent duplicate entries, I receive the following error:</p>
  
  <p><em>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</em></p>
  
  <p>This error was received by executing:</p>
  
  <pre><code>update dx set cicd9='721.90', cdesc='osteoarthritis of spine, nos' where cicd9 = '721.90' and cdesc=' osteoarthritis of spine, nos';
  </code></pre>
  
  <p>(The attempt here is to remove the spaces in cdesc.)</p>
  
  <p>If I "Return Null" from the update in the trigger, then everything gets deleted!?</p>
  
  <p>How should the trigger be written to allow the calling "update dx" to work and yet prevent the result causing a duplicate key?</p>
  
  <p>(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??).</p>
  
  <p>What am I doing wrong?</p>
  
  <p>Below is the trigger I am working with:</p>
  
  <pre><code>  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();
  </code></pre>
  
  <p>The constraint in dx is:</p>
  
  <pre><code>CONSTRAINT noduplicate_dx UNIQUE (cicd9, cdesc, groupid, tposted),
  </code></pre>
  
  <p>TIA</p>