Oracle触发器联合唯一约束

Oracle支持可为空字端的唯一约束呢?下面就是用触发器作出的限制语句,仅供参考:
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;
posted @ 2020-09-24 16:55  好奇成传奇  阅读(385)  评论(0编辑  收藏  举报