Trigger |
尚未結案
|
hsiuchih
一般會員 發表:6 回覆:10 積分:3 註冊:2004-04-20 發送簡訊給我 |
|
GrandRURU
站務副站長 發表:240 回覆:1680 積分:1874 註冊:2005-06-21 發送簡訊給我 |
妳可以把你pl/sql的程式內容放上來看看嗎?
===================引 用 hsiuchih 文 章=================== 各位高高手,想來跟大家請教一個問題, 就是小妹我第一次寫Trigger,在RUN時卻出現了一個不明白的錯誤訊息, 想請教各位高高手,是否有人可以指導一下下哩! 謝謝各位唷! Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01 Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'
|
herbert2
尊榮會員 發表:58 回覆:640 積分:894 註冊:2004-04-16 發送簡訊給我 |
網站自7/23起發文就很少, 直至今日才爆量, 可見應該是有問題.
您的 Trigger 可能是變數前漏加冒號『:』故 Compile 不 Pass. 寫成『:NEW.WEIGHT_NOW』應可過關. =引 用 hsiuchih 文 章=================== 各位高高手,想來跟大家請教一個問題, 就是小妹我第一次寫Trigger,在RUN時卻出現了一個不明白的錯誤訊息, 想請教各位高高手,是否有人可以指導一下下哩! 謝謝各位唷! Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01 Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'
|
hsiuchih
一般會員 發表:6 回覆:10 積分:3 註冊:2004-04-20 發送簡訊給我 |
各位前輩,之前發生的問題已經搞定了!
但是,現在卻有一個很詭異的情形發生, 就是有個程式去update Table時,竟然Trigger沒有起來, 可是,如果是其他程式的話,都可以正常的執行Trigger, 一直找不到問題在哪裡......... create or replace trigger Append_SSMA01MST01 after insert or update on ssm401dtl01 referencing new as new old as old for each row declare row_count number(1); /*判斷訂單是否已經存在*/ Lv_Cust_No varchar2(10); Lv_Cust_Abbr varchar2(10); Lv_Buyer_No varchar2(10); Lv_Buyer_Abbr varchar2(10); Lv_Currency varchar2(5); Lv_Contract_No varchar2(12); Lv_Date_Sign_Contract date; Lv_Sale_Area_Code varchar2(1); Lv_Sale_Area_Group varchar2(10); Lv_Sales_ID varchar2(8); Lv_Sales_Name varchar2(10); pragma autonomous_transaction; begin begin select cust_abbreviations into Lv_Cust_Abbr from ssm001mst01 where cust_no = :new.cust_no; insert into pub002mst01(prono,emp_id,date_log,log_string,log_status) values('SSM_Trigger','X8899',sysdate,'AAA','X'); select count(*) into row_count from ssma01mst01 where sale_order = :new.sale_order and sale_item = :new.sale_item; insert into pub002mst01(prono,emp_id,date_log,log_string,log_status) values('SSM_Trigger','X8899',sysdate,'QQQ','M'); If row_count = 0 Then begin insert into pub002mst01(prono,emp_id,date_log,log_string,log_status) values('SSM_Trigger','X8899',sysdate,'OOO','Y'); insert into ssma01mst01(sale_order,sale_item,cust_no,cust_abbreviations,mic_no,sale_order_dia,mtrl_no, sale_order_weight,sale_order_unit_price,sale_order_thick, sale_order_width, date_delivery_pp,date_delivery_sales,pack_code, certificate_code, max_of_total_weight,min_of_total_weight,max_weight_of_each_prod, min_weight_of_each_prod, sale_order_length,cust_special,resell_no,cust_purchase_order, color, cold_drawn,mech_elmt,mech_elmt_remark,hot_rolled_thick,slab_length, asign_length,flag_sub_volumes,theory_weight,block_number,thick_max, thick_min,flag_length_diff,hot_rolled_size,cut_off_times,flag_test_plan, consignee_no,mic_count,weight_now,billet_size,shave_size,shave_no_order,shave_seq_order, po_flag,po_date,cycle_no,bom_ver,heat_no,pcs_plan,finish_color,roll_length, range1_eye,range2_eye,range3_eye,range4_eye,show_lineup_desc,flag_oem, oem_order_no,oem_shop_code,oem_reserved) values(:new.sale_order,:new.sale_item,:new.cust_no,Lv_Cust_Abbr,:new.mic_no,:new.sale_order_dia, :new.mtrl_no,:new.sale_order_weight,:new.sale_order_unit_price,:new.sale_order_thick, :new.sale_order_width,:new.date_delivery_pp,:new.date_delivery_sales,:new.pack_code, :new.certificate_code,:new.max_of_total_weight,:new.min_of_total_weight,:new.max_weight_of_each_prod, :new.min_weight_of_each_prod,:new.sale_order_length,:new.cust_special,:new.resell_no, :new.cust_purchase_order,:new.color,:new.cold_drawn,:new.mech_elmt,:new.mech_elmt_remark, :new.hot_rolled_thick,:new.slab_length,:new.asign_length,:new.flag_sub_volumes,:new.theory_weight, :new.block_number,:new.thick_max,:new.thick_min,:new.flag_length_diff,:new.hot_rolled_size, :new.cut_off_times,:new.flag_test_plan,:new.consignee_no,:new.mic_count,:new.pp_now_weight,:new.pp_billet_size, :new.pp_shave_size,:new.pp_shave_no_order,:new.pp_shave_seq_order,:new.pp_flag_po,:new.pp_date_po, :new.pp_cycle_no,:new.pp_bom_ver,:new.pp_heat_no,:new.pp_plan_pcs,:new.pp_flag_finish_color, :new.pp_roll_length,:new.pp_eye_dia_range_1,:new.pp_eye_dia_range_2, :new.pp_eye_dia_range_3,:new.pp_eye_dia_range_4,:new.pp_show_lineup_desc,:new.flag_oem,:new.pp_oem_order_no, :new.oem_shop_code,:new.pp_oem_reserved); end; Elsif row_count <> 0 Then begin insert into pub002mst01(prono,emp_id,date_log,log_string,log_status) values('SSM_Trigger','X8899',sysdate,'ZZZ','Y'); select decode(substr(a.sale_order,1,2),'0X',b.cust_no,'0Y',b.cust_no,a.cust_no), c.cust_abbreviations, decode(substr(a.sale_order,1,2),'0X',b.cust_no,'0Y',b.cust_no,a.consignee_sale_order), d.cust_abbreviations,a.sale_order_currency,a.contract_no,e.date_sign_contract, c.sale_area_code,f.sale_area_group,a.sales_id,g.sales_name into Lv_Cust_No,Lv_Cust_Abbr,Lv_Buyer_No,Lv_Buyer_Abbr,Lv_Currency,Lv_Contract_No, Lv_Date_Sign_Contract,Lv_Sale_Area_Code,Lv_Sale_Area_Group,Lv_Sales_ID,Lv_Sales_Name from ssm401mst01 a,ssm401dtl01 b,ssm001mst01 c,ssm001mst01 d,ssm301mst01 e, ssm001map02 f,ssm001map06 g where a.sale_order = b.sale_order( ) and a.cust_no = c.cust_no( ) and a.consignee_sale_order = d.cust_no( ) and a.contract_no = e.contract_no( ) and c.sale_area_code = f.sale_area_code( ) and a.sales_id = g.sales_id( ) and a.sale_order = :new.sale_order and b.sale_item = :new.sale_item; update ssma01mst01 set cust_no = Lv_Cust_No,cust_abbreviations = Lv_Cust_Abbr, consignee_sale_order = Lv_Buyer_No,consignee = Lv_Buyer_Abbr, currency = Lv_Currency,contract_no = Lv_Contract_No, contract_no_sign_date = Lv_Date_Sign_Contract, sale_area_code = Lv_Sale_Area_Code,sale_area_group = Lv_Sale_Area_Group, sales_id = Lv_Sales_ID,sales_name = Lv_Sales_Name,mic_no = :new.mic_no, sale_order_dia = :new.sale_order_dia,mtrl_no = :new.mtrl_no, sale_order_weight = :new.sale_order_weight, sale_order_unit_price = :new.sale_order_unit_price, sale_order_thick = :new.sale_order_thick,sale_order_width = :new.sale_order_width, date_delivery_pp = :new.date_delivery_pp,date_delivery_sales = :new.date_delivery_sales, pack_code = :new.pack_code,certificate_code = :new.certificate_code, max_of_total_weight = :new.max_of_total_weight,min_of_total_weight = :new.min_of_total_weight, max_weight_of_each_prod = :new.max_weight_of_each_prod, min_weight_of_each_prod = :new.min_weight_of_each_prod, sale_order_length = :new.sale_order_length,cust_special = :new.cust_special, resell_no = :new.resell_no,cust_purchase_order = :new.cust_purchase_order, color = :new.color,cold_drawn = :new.cold_drawn,mech_elmt = :new.mech_elmt, mech_elmt_remark = :new.mech_elmt_remark,hot_rolled_thick = :new.hot_rolled_thick, slab_length = :new.slab_length,asign_length = :new.asign_length, flag_sub_volumes = :new.flag_sub_volumes,theory_weight = :new.theory_weight, block_number = :new.block_number,thick_max = :new.thick_max,thick_min = :new.thick_min, flag_length_diff = :new.flag_length_diff,hot_rolled_size = :new.hot_rolled_size, cut_off_times = :new.cut_off_times,flag_test_plan = :new.flag_test_plan, consignee_no = :new.consignee_no,mic_count = :new.mic_count,weight_now = :new.pp_now_weight, billet_size = :new.pp_billet_size,shave_size = :new.pp_shave_size, shave_no_order = :new.pp_shave_no_order,shave_seq_order = :new.pp_shave_seq_order, po_flag = :new.pp_flag_po,po_date = :new.pp_date_po,cycle_no = :new.pp_cycle_no, bom_ver = :new.pp_bom_ver,heat_no = :new.pp_heat_no,pcs_plan = :new.pp_plan_pcs, finish_color = :new.pp_flag_finish_color,roll_length = :new.pp_roll_length,range1_eye = :new.pp_eye_dia_range_1, range2_eye = :new.pp_eye_dia_range_2,range3_eye = :new.pp_eye_dia_range_3,range4_eye = :new.pp_eye_dia_range_4, show_lineup_desc = :new.pp_show_lineup_desc,flag_oem = :new.flag_oem,oem_order_no = :new.pp_oem_order_no, oem_shop_code = :new.oem_shop_code,oem_reserved = :new.pp_oem_reserved,flag_status = :new.flag_pp, flag_final_status = :new.flag_done where sale_order = :new.sale_order and sale_item = :new.sale_item; end; End if; Commit; Exception when others then null; end; end;
|
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |