|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
|
|||
|
|||
|
Trigger Modification
This was my origibnal trigger. I added another condition to it. Now it is not giving me any compilation or runtime errors but it is not doing the required update. Any suggestions?
Original Trigger WHENEVER SQLERROR EXIT SQL.SQLCODE; CREATE OR REPLACE TRIGGER ship_frt_sts_upd BEFORE UPDATE OF STATUS ON SHIPMENT FOR EACH ROW WHEN (new.status in ('A', 'T', 'P')) DECLARE cnt integer; BEGIN SELECT count(*) INTO cnt FROM ord o, ord_load ol WHERE :new.id = ol.load_id AND :new.sched_num = ol.sched_num AND ol.stop_num = 1 AND ol.ord_id = o.id AND ol.sched_num = o.sched_num AND (o.pass_char2 != 'RTD'); if (cnt > 0) then :new.status := 'L'; end if; END; / EXIT SUCCESS; New Trigger WHENEVER SQLERROR EXIT SQL.SQLCODE; CREATE OR REPLACE TRIGGER frt_sts_ship_bur BEFORE UPDATE OF STATUS ON SHIPMENT FOR EACH ROW WHEN (new.status in ('E','K','P','T','Y','X')) DECLARE orm_cnt integer; rtd_cnt integer; temp_bol_id varchar2(17); BEGIN if (:new.status in ('K','Y')) then SELECT count(*) INTO orm_cnt FROM ord o, ord_load ol WHERE :new.id = ol.load_id AND :new.sched_num = ol.sched_num AND ol.stop_num = 1 AND ol.ord_id = o.id AND ol.sched_num = o.sched_num AND (o.pass_char2 = 'ORM'); if (orm_cnt > 0) then :new.status := 'Z'; end if; end if; if (:new.status in ('E','P','T','X')) then SELECT count(*) INTO rtd_cnt FROM ord o, ord_load ol WHERE :new.id = ol.load_id AND :new.sched_num = ol.sched_num AND ol.stop_num = 1 AND ol.ord_id = o.id AND ol.sched_num = o.sched_num AND (o.pass_char2 != 'RTD'); if (rtd_cnt > 0) then :new.status := 'Z'; end if; end if; if ((:new.status in ('K','Y','T')) and ( ld.external_bol is null)) thenSELECT '00000491'||SUBSTR(bol_id,4,8)|| to_char(mod(10 - (mod((((to_number(SUBSTR(bol_id,5,1)) + to_number(SUBSTR(bol_id,7,1)) + to_number(SUBSTR(bol_id,9,1)) + to_number(SUBSTR(bol_id,11,1)) + 5)*3) + ((to_number(SUBSTR(bol_id,4,1)) + to_number(SUBSTR(bol_id,6,1)) + to_number(SUBSTR(bol_id,8,1)) + to_number(SUBSTR(bol_id,10,1)) + 9))),10)),10)) INTO temp_bol_id FROM shipment, load_stop WHERE id = :new.id AND shipment.sched_num = :new.sched_num AND shipment.id = load_stop.load_id AND shipment.sched_num = load_stop.sched_num AND stop_type = 'DL'; :new.external_bol := temp_bol_id; end if; END; / EXIT SUCCESS; |
|
#2
|
|||
|
|||
|
Your trigger seems to work fine, very hard to predict about the logical mistake. What is your table staucture?. I mean I have seen in your trigger that you are comapring value with a String, for example:
AND stop_type = 'DL'; Please first trim/remove the all space before comparing it with any string if stop_type length is defined more than two character in table and others as well. |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Trigger Modification |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|