全國最多中醫師線上諮詢網站-台灣中醫網
發文 回覆 瀏覽次數:1250
推到 Plurk!
推到 Facebook!

Trigger

尚未結案
hsiuchih
一般會員


發表:6
回覆:10
積分:3
註冊:2004-04-20

發送簡訊給我
#1 引用回覆 回覆 發表時間:2010-07-23 08:25:45 IP:203.67.xxx.xxx 訂閱
 各位高高手,想來跟大家請教一個問題,
就是小妹我第一次寫Trigger,在RUN時卻出現了一個不明白的錯誤訊息,
想請教各位高高手,是否有人可以指導一下下哩!
謝謝各位唷!

Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01
Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'
GrandRURU
站務副站長


發表:234
回覆:1651
積分:1742
註冊:2005-06-21

發送簡訊給我
#2 引用回覆 回覆 發表時間:2010-08-03 12:49:53 IP:203.75.xxx.xxx 訂閱
妳可以把你pl/sql的程式內容放上來看看嗎?

===================引 用 hsiuchih 文 章===================
各位高高手,想來跟大家請教一個問題,
就是小妹我第一次寫Trigger,在RUN時卻出現了一個不明白的錯誤訊息,
想請教各位高高手,是否有人可以指導一下下哩!
謝謝各位唷!

Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01
Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'
herbert2
尊榮會員


發表:58
回覆:632
積分:878
註冊:2004-04-16

發送簡訊給我
#3 引用回覆 回覆 發表時間:2010-08-03 23:53:46 IP:202.39.xxx.xxx 訂閱
網站自7/23便無法回覆, 簡訊發出後變空白.
Mail 給服務中心及副站長也都石沉大海.
網站自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

發送簡訊給我
#4 引用回覆 回覆 發表時間:2010-08-04 08:20:18 IP:60.248.xxx.xxx 訂閱
各位前輩,之前發生的問題已經搞定了!
但是,現在卻有一個很詭異的情形發生,
就是有個程式去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;
系統時間:2017-10-18 22:33:49
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!