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_triggerbefore inserton transfor each rowdeclare  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_triggerbefore dropon schemabegin  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 Testfor each rowbegin   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);