一个Oracle触发器的例子

有一个表，表名是xx，有ABCD四个字段，正常情况下，ABC、ABD这三个字段都可以唯一确定一条记录，按理应该做成唯一索引，但由于历史原因，该表存在重复数据，但要删掉哪一条需要人工判断，无法用语句批量删除，于是唯一索引加不上。但为了保证以后数据的准确性，需要控制新插进去的记录是唯一的。于是我写了一个触发器，当新插进去的记录与现有记录重复时就报错：

 

CREATE global temporary TABLE g_xx_temp
 (flatdocid     int,
  modeldocid     int,flatdocrev int,modeldocrev int) ON COMMIT DELETE ROWS;

CREATE OR REPLACE TRIGGER trg_xx_row
  before INSERT or update
  ON xx
  FOR EACH ROW
  DECLARE
    -- local variables here
  BEGIN
    insert into g_xx_temp(flatdocid,modeldocid,flatdocrev,modeldocrev)
      values(:NEW.flatdocid,:NEW.modeldocid,:NEW.flatdocrev,:NEW.modeldocrev);
  END;

CREATE OR REPLACE TRIGGER trg_xx
  after INSERT or update
  ON xx
  --FOR EACH ROW
  DECLARE
    -- local variables here
    v_count int;
    v_flatdocid int;
    v_modeldocid int;
    v_flatdocrev int;
    v_modeldocrev int;
    CURSOR c_temp IS SELECT flatdocid,modeldocid,flatdocrev,modeldocrev from g_xx_temp;
  BEGIN

    OPEN c_temp; 
    LOOP 
      FETCH c_temp INTO v_flatdocid,v_modeldocid,v_flatdocrev,v_modeldocrev;
      select count(1) into v_count from xx
        where flatdocid=v_flatdocid and modeldocid=v_modeldocid and flatdocrev=v_flatdocrev;
      if (v_count>=2) then
         RAISE_APPLICATION_ERROR(-20001, '非法数据1：flatdocid='||to_char(v_flatdocid)||',modeldocid='||to_char(v_modeldocid)||',flatdocrev='||to_char(v_flatdocrev));
      end if;
     
      select count(1) into v_count from xx
        where flatdocid=v_flatdocid and modeldocid=v_modeldocid and modeldocrev=v_modeldocrev;
      if (v_count>=2) then
         RAISE_APPLICATION_ERROR(-20001, '非法数据1：flatdocid='||to_char(v_flatdocid)||',modeldocid='||to_char(v_modeldocid)||',modeldocrev='||to_char(v_modeldocrev));
      end if;
     
      EXIT WHEN c_temp%NOTFOUND;
    end loop;
   
    CLOSE  c_temp;
    
  END;