Oracle触发器里面怎样得到当前新的纪录的值

Question

:new.colname 里面就是新插入行的信息

Answer 1

EXECUTE IMMEDIATE
  'Select Count(*) FROM err_cdr' into Record_no;
修改如下：EXECUTE IMMEDIATE
  'Select Count(*) into '||Record_no||' FROM err_cdr';

Answer 2

EXECUTE IMMEDIATE
  'Select To_char(sysdate,'yyyymmdd') FROM dual' into Cur_date;
修改如下：EXECUTE IMMEDIATE
  'Select To_char(sysdate,''yyyymmdd'') into '||Cur_date||' FROM dual';

Answer 3

呵呵来晚了
但是必须设定伪指令的新值指令为new
你设定为new就是new,不然是你设置的指令
默认情况是new
:)
xuexi

Answer 4

:New.TRANSPARENT_PARAMETER , :New.SEQUENCE_NUMBER , "" , :New.CONNECTED_NUMBER_INFO , :New.BCG_IDENTITY ,||修改如下::New.TRANSPARENT_PARAMETER , :New.SEQUENCE_NUMBER , '' , :New.CONNECTED_NUMBER_INFO , :New.BCG_IDENTITY ,||

Answer 5

我现在把代码改为如今的修改，去掉插入表err_cdr那部分代码外编译都正确，请各位帮忙看看插入数据库的那部分代码出现了什么问题！源代码如下：declare   
  cur_date varchar2(14);
  error_no varchar2(5);
  record_no number(10);
  start_time varchar2(14);
  end_time varchar2(14);
  start_time_l number(10);
  end_time_l number(10); 
  cur_time_l number(10);
BEGIN  
  SELECT TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS') INTO cur_date FROM DUAL;
  error_no :='40011';
  start_time := :new.answer_date;
  end_time := :new.end_date;
  start_time_l :=to_number(substr(start_time,1,4));
  end_time_l :=to_number(substr(end_time,1,4));
  cur_time_l :=to_number(substr(cur_date,1,4));
  EXECUTE IMMEDIATE 
  'Select Count(*) into '||Record_no||' FROM err_cdr';
  if start_time_l > end_time_l or start_time_l > cur_time_l or end_time_l > cur_time_l then
  Insert Into err_cdr(
       FILE_NAME,
       RECORD_NO,
       ERROR_NO,
       RECORD_TYPE,
       RECORD_INDICATOR,
       CALL_NUMBER_ADDRESS,
       CALL_NUMBER,
       LOCATION_NUMBER_ADDRESS,
       LOCATION_NUMBER,
       CALLED_NUMBER_ADDRESS,
       CALLED_NUMBER,
       TRANSLATED_NUMBER_ADDRESS,
       TRANSLATED_NUMBER,
       CHARGED_PARTY_IDENTITY,
       BILLED_NUMBER_ADDRESS,
       BILLED_NUMBER,
       IN_TRUNK,
       OUT_TRUNK,
       ANSWER_DATE,
       END_DATE,
       CONVERSATION_DURATION,
       FEE,
       US1_COUNTER,
       UUS3_COUNTER,
       CHARGE_CLASS,
       TRAFFIC_TYPE,
       CALL_END_CAUSE,
       VALIDATION_INDICATOR,
       CLOCK_INDICATOR,
       CHARGE_FREE_INDICATOR,
       CALL_ATTEMPT_INDICATOR,
       CALLING_PARTY_CATEGORY,
       BEARER_SERVICE,
       TELESERVICE,
       IN_SURCHARGE,
       CHARGE_MODULATOR,
       TRANSPARENT_PARAMETER,
       SEQUENCE_NUMBER,
       SPARE,
       CONNECTED_NUMBER_INFO,
       BCG_IDENTITY,
       CALLING_PARTY_PRV_NBR,
       CALLED_PARTY_PRV_NBR,
       CHARGE_IDD_DDD,
       SS_CALL
       )
     values
          (:new.FILE_NAME,
           record_no,
           error_no,
           :new.RECORD_TYPE ,
           :new.RECORD_INDICATOR,
           :new.CALL_NUMBER_ADDRESS,
           :new.CALL_NUMBER,
           :new.LOCATION_NUMBER_ADDRESS,
           :new.LOCATION_NUMBER,
           :new.CALLED_NUMBER_ADDRESS,
           :new.CALLED_NUMBER,
           :new.TRANSLATED_NUMBER_ADDRESS,
           :new.TRANSLATED_NUMBER,
           :new.CHARGED_PARTY_IDENTITY,
           :new.BILLED_NUMBER_ADDRESS,
           :new.BILLED_NUMBER,
           :new.IN_TRUNK,
           :new.OUT_TRUNK,
           :new.ANSWER_DATE,
           :new.END_DATE,
           :new.CONVERSATION_DURATION,
           :new.FEE,
           :new.UUS1_COUNTER,
           :new.UUS3_COUNTER,
           :new.CHARGE_CLASS,
           :new.TRAFFIC_TYPE,
           :new.CALL_END_CAUSE,
           :new.VALIDATION_INDICATOR,
           :new.CLOCK_INDICATOR,
           :new.CHARGE_FREE_INDICATOR,
           :new.CALL_ATTEMPT_INDICATOR,
           :new.CALLING_PARTY_CATEGORY,
           :new.BEARER_SERVICE,
           :new.TELESERVICE,
           :new.IN_SURCHARGE,
           :new.CHARGE_MODULATOR,
           :new.TRANSPARENT_PARAMETER,
           :new.SEQUENCE_NUMBER,
           '',
           :new.CONNECTED_NUMBER_INFO,
           :new.BCG_IDENTITY,
           :new.CALLING_PARTY_PRV_NBR,
           :new.CALLED_PARTY_PRV_NBR,
           :new.CHARGE_IDD_DDD,
           :new.SS_CALL
           ); 
  end if;  
End;

Answer 6

show err命令查看错误详细信息