oracle触发器中定义变量

create  or  replace  trigger  t_table_triger
  after  insert  on  t_table 
   for  each row
declare  
   msg  varchar2 ( 22 );
   returnMsg  varchar2 ( 180 );
   xsBH  varchar2 ( 10 );
   sourceUserTpCount  number : = 0 ;
begin  
    if  inserting  then    -- inserting表示插入操作，每种操作类型都有对应的关键字
      msg: = :NEW.msg;  -- 获取触发事件插入的数据值
      
      msg: = Replace (msg, ' + ' , '' ); -- 变量赋值
      msg: = Replace (msg, ' - ' , '' ); -- 替换，跟MSSQL里函数相同
 
       if  LOWER (SUBSTR(msg, 1 , 1 )) = ' v '  and  LENGTH(msg) > 3  then   
           if   sysdate < to_date( ' 2009-12-26 ' , ' yyyy-MM-dd ' )  then  -- 时间比较，sysdate是当前时间                                           
              select    count ( * )  into  sourceUserTpCount  from  t_siteuser  where  userid = sourceUser  and  TO_CHAR(gentime, ' YYYYMMDD ' ) =  TO_CHAR(sysdate, ' YYYYMMDD ' ); 
              if  sourceUserTpCount <= 20  then   
                dbms_output.put_line(xsBH); -- 打印信息
                      insert  into  t_siteuser(id,bh,gentime)  values  (SEQ_t_siteuser.nextval,xsBH,sysdate); -- SEQ_t_siteuser.nextval获取标识ID
                     returnMsg: = ' 感谢您对 ' || xsBH || ' 号的支持！ ' ; -- 字符串与变量结合             
              else
                returnMsg: = ' 对不起，网站每天限投20票！ ' ;
              end  if ;  
           else
                returnMsg: = ' 活动已结束，欢迎您继续关注！ ' ;
           end  if  ;  
       end  if ;     
     end  if ;  
end ;

----------------------------------------------------------
--每进行一次交易，就要调用触发器，自动扣除或增加账户金额
----------------------------------------------------------
create table account
(
  customerName varchar2(30) primary key,
  cardID varchar2(8),
  currentMoney number
);
insert into account values('Daivd','10010001',5000);
insert into account values('Jason','10010002',3000);

create table trans
(
  transDate date,
  cardID varchar2(8),
  transType varchar2(10),
  transMoney number
);
insert into trans 
values(sysdate,'10010001','取款',1000);

create or replace trigger trans_trigger
before insert
on trans
for each row
declare
  v_currentMoney account.currentMoney%type;
begin
  --判断类型
  if :new.transType='取款' then
   --取款
   select currentMoney into v_currentMoney
   from account
   where cardID=:new.cardID;
   
   if v_currentMoney < :new.transMoney then
     raise_application_error(-20001,'余额不足');
   end if;
   
   update account
   set currentMoney=currentMoney-:new.transMoney
   where cardID=:new.cardID;
  else
   --存款
   update account
   set currentMoney=currentMoney+:new.transMoney
   where cardID=:new.cardID;
  end if;
exception  
  when no_data_found then
    raise_application_error(-20002,'无效的帐户');
end;


--模式(schema)级触发器
create or replace trigger schema_trigger
before drop
on schema
begin
  dbms_output.put_line('schema_trigger called');
  dbms_output.put_line(ora_dict_obj_name);
  dbms_output.put_line(ora_dict_obj_type);
  
  if ora_dict_obj_name='ACCOUNT' then
    raise_application_error(-20003,'ACCOUNT表不能被删除');
  end if;
end;

drop table account;
--ora_dict_obj_name 操作对象名称
--ora_dict_obj_type 操作对象类型

--启用触发器
alter trigger schema_trigger enable;
--禁用触发器
alter trigger schema_trigger disable;

-------------------------自己写的-------------------

-----------------------测试表---------------------
create table Test
(
iKey int,
iValue int,
primary key(iKey)
);
create table Test_Log
(
iKey int,
iClass varchar(4)
);
create trigger Test_Tri after insert on Test
for each row
begin
   if :new.iValue>90 then
   insert into Test_log values(:new.iKey,1);
   end if;
end;

insert into Test values(12,120);
insert into Test values(18,60);
insert into Test values(10,90);