SunQuest
           Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
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  
Old July 20th, 2004, 09:43 AM
amit21 amit21 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 1 amit21 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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)) then
SELECT '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;

Reply With Quote
  #2  
Old July 20th, 2004, 02:42 PM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > Trigger Modification


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway