触发器限制联合唯一约束，字段可为空

--创建唯一性触发器
CREATE OR REPLACE TRIGGER Tg_Completion_Test
  BEFORE INSERT OR UPDATE ON bz_funds_voucher
  FOR EACH ROW --行触发，没有本行则为语句级触发
DECLARE
  Too_Many EXCEPTION;
  PRAGMA EXCEPTION_INIT(Too_Many, -20001);
  v_Exist_Count NUMBER := 0;
BEGIN
    Select Count(*)
      Into v_Exist_Count
      From bz_funds_voucher t
     Where t.document_code is not null
       and t.territory_code = :new.territory_code
       and t.document_code = :new.document_code ;
  If v_Exist_Count > 0 Then
    Raise_Application_Error(-20001, '数据重复存储!');
  End If;
 End;

 

 

 

CREATE OR REPLACE TRIGGER Tg_Bz_rent_payment_voucher
  BEFORE INSERT OR UPDATE ON bz_rent_payment_voucher
  FOR EACH ROW --行触发，没有本行则为语句级触发
DECLARE
  Too_Many EXCEPTION;
  PRAGMA EXCEPTION_INIT(Too_Many, -20002);
  v_Exist_Count NUMBER := 0;
BEGIN
    Select Count(*)
      Into v_Exist_Count
      From bz_rent_payment_voucher t
     Where
     t.collection_way not in ('转款', '优惠活动')
           and t.create_date > to_date('2015-01-01', 'yyyy-mm-dd')
     and t.serial_no is not null
           and t.reserve_code is null
           and t.serial_no = :new.serial_no ;
  If v_Exist_Count > 0 Then
    Raise_Application_Error(-20002, '同一流水收款单重复存储!');
  End If;
 End;