限售触发器（写触发器参考）

CREATE OR REPLACE TRIGGER TRI_Mg_LockShares
  before insert or update on TBL_CHN_T_Mg_LockShares
  for each row

declare
  msgNum number(10);
begin
  --InstitutionID  上市公司ID
  if INSERTING then
    select InstitutionID
      into :new.InstitutionID
      from INPUT.TBL_CHN_I_CO_STOCKINFO@Input_52
     where SYMBOL = :new.SYMBOL
       and UPDATESTATE <= 1
       and rownum = 1;
--FullName  股东名称
    if instr(:new.FullName, '?') > 0 then
      :new.FullName := replace(:new.FullName, '?', '·');
    end if;
    if regexp_instr(:new.reason, '(首发后)+.*(限售)+') > 0 then
      select count(FullName)
        into msgNum
        from TBL_CHN_T_Mg_LockShares a
       where a.updatestate <= 1
         and a.FullName = :new.FullName
         and a.Symbol = :new.symbol
         and FullName in (select FullName
                            from TBL_CHN_T_Mg_IpoShr
                           where updatestate <= 1);
--reason  限售原因
      if msgNum > 0 then
        select '首发原股东' into :new.reason from dual;
      else
        select '非公开增发' into :new.reason from dual;
      end if;
    elsif regexp_instr(:new.reason, '(追加承诺)+') > 0 then
      select count(FullName)
        into msgNum
        from TBL_CHN_T_Mg_LockShares a
       where a.updatestate <= 1
         and a.FullName = :new.FullName
         and a.Symbol = :new.symbol
         and FullName in (select FullName
                            from TBL_CHN_T_Mg_IpoShr
                           where updatestate <= 1);

      if msgNum > 0 then
        select '首发原股东' into :new.reason from dual;
      else
        select '其他' into :new.reason from dual;
      end if;
    end if;

--reasonid  限售原因编码
    select replace(replace(replace(replace(replace(replace(replace(replace(replace(:new.Reason,
                                                                                   '非公开增发',
                                                                                   'P6403'),
                                                                           '公开增发',
                                                                           'P6404'),
                                                                   '其他',
                                                                   'P6409'),
                                                           '股权激励',
                                                           'P6408'),
                                                   '高管限售',
                                                   'P6407'),
                                           '股权分置改革',
                                           'P6406'),
                                   '配股',
                                   'P6405'),
                           '首发网下配售',
                           'P6402'),
                   '首发原股东',
                   'P6401')
      into :new.reasonid
      from dual;

--beginningshares  期初持有数量
    if :new.beginningshares is null then
      select '0' into :new.beginningshares from dual;
    end if;


--addshares  本期增加限售股数
    if :new.addshares is null then
      select '0' into :new.addshares from dual;
    end if;


--listedshares  本期解禁数量
    if :new.listedshares is null then
      select '0' into :new.listedshares from dual;
    end if;

--endshares  期末持有限售股数
    if :new.endshares is null then
      select '0' into :new.endshares from dual;
    end if;

  end if;

end;