幾個ORACLE個觸發器!

create or replace trigger tri_SERVICE_TYPE
before Delete on SMS_SERVICE_TYPE
for each row
Declare

   Cant_Delete Exception;
Begin
   If Deleting Then
      --不允許刪除
      Raise Cant_Delete;
   End If;
   Exception
   When Cant_Delete Then
      Raise_Application_Error(-20007, '不允許刪除操作'); --Raise_Application_Error的用法在07-11-29日誌中有介紹。
End;

/

create or replace trigger tri_message302
before Insert on sms_message302
for each row
Declare
v_num1 number(10);
v_num2 number(10);
Begin
If Inserting Then
   v_num1 :=instr(:New.Content,':',1,1);
   v_num2 :=instr(:New.Content,'：',1,1);
   If v_num1 > 0 Then

    :New.Content := replace(:New.Content,substr(:New.Content,1,v_num1-1),'影視報道');
   Elsif v_num1 = 0 And v_num2 > 0 Then
    :New.Content := replace(:New.Content,substr(:New.Content,1,v_num2-1),'影視報道');
   End If;

End If;

Exception
When Others Then
   Null;
End tri_message302;
/

create or replace trigger TRI_MISCRULE12
before insert or update on SMS_MISC_RULE_1_2
for each row
declare
SEQNO NUMBER(6);
begin

IF Upper(:new.MISCORDER) IN ('OTHERS','NULL') then
    :new.MISCORDER:=Upper(:new.MISCORDER);
    :new.ORDERlen:=0;
ELSE
    :new.MISCORDER:=Upper(:new.MISCORDER);
    :new.ORDERlen:=length(:new.MISCORDER);
END IF;
IF upper(:new.SMSCODE)='NORMAL' then
    :new.SMSCODE:='NORMAL';
:new.SMSMODE:=0;
:new.SMSLEN:=0;
ELSE
   :new.SMSLEN:=Length(:new.SMSCODE);
END IF;
   If Nvl(To_Char(:New.Ruleid), 'NULL') = 'NULL' Then
       SELECT SMS_MISC_RULESEQ.NEXTVAL INTO SEQNO FROM DUAL;
       :new.RULEID:=SEQNO;
   End If;

end TRI_MISCRULE12;
/