By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Embedded SQL returning uncatchable error - Procedure/Trigger

P: 1
I am getting a frustrating error when i try to insert into the table the trigger i created is on.

Any insight would be appreciated

Error:
Expand|Select|Wrap|Line Numbers
  1. db2 => INSERT INTO trstat VALUES ('340758A',     'POSITION', timestamp ( '2008-08-14-13.53.38.000000' ), '-',             272816, '[0432642N0794126W]No Zone Match',
  2.             'TM4WIN',                    365374, 'ONOAK')
  3. DB21034E  The command was processed as an SQL statement because it was not a
  4. valid Command Line Processor command.  During SQL processing it returned:
  5. SQL0723N  An error occurred in a triggered SQL statement in trigger
  6. "USER.RCC_PTIMEST".  Information returned for the error includes SQLCODE
  7. "-746", SQLSTATE "57053" and message tokens
  8. "USER.RCC_PROC_PTIMESTAMP|SQL08081417151250".  SQLSTATE=09000
  9.  
Code
Expand|Select|Wrap|Line Numbers
  1. --#SET TERMINATOR @ 
  2. DROP TRIGGER rcc_ptimestamp
  3. @
  4. DROP PROCEDURE rcc_proc_ptimestamp
  5. @
  6.  
  7. CREATE PROCEDURE rcc_proc_ptimestamp(
  8.     IN p_code            VARCHAR(10),
  9.     IN p_new_time    TIMESTAMP,
  10.     IN p_trip_num    INTEGER,
  11.     IN p_status        VARCHAR(10)
  12. )
  13.     LANGUAGE SQL
  14.     MODIFIES SQL DATA
  15.  
  16.     P1:BEGIN ATOMIC
  17.  
  18.     DECLARE v_count1 INTEGER DEFAULT 0;
  19.     DECLARE v_count2 INTEGER DEFAULT 0;
  20.     DECLARE    v_old_time    TIMESTAMP;
  21.     DECLARE SQLCODE INTEGER DEFAULT 0;
  22.  
  23.     SELECT count(time_changed) INTO v_count2 FROM trstat WHERE trip_9 = p_trip_num AND code = p_code AND status = 'ENRTE';
  24.     SELECT time_changed INTO v_old_time FROM trstat WHERE trip_9 = p_trip_num AND code = p_code AND status = 'ENRTE';  
  25.  
  26.     IF p_status = 'ENRTE' THEN
  27.         --ENTER INTO rcc_ptimestamp --current En Routed trips
  28.         INSERT INTO rcc_ptimestamp (trip_number, start_time) VALUES (p_trip_num, p_new_time);
  29.     ELSEIF v_count2 <> 0 THEN            
  30.         SELECT count(*) INTO v_count1 FROM rcc_timestamp WHERE trip_number = p_trip_num AND start_time = v_old_time;
  31.  
  32.                 --verify no duplicates in historical table
  33.         IF v_count1 = 0 THEN
  34.             --match
  35.              --INSERT INTO rcc_timestamp
  36.              INSERT INTO rcc_timestamp (trip_number, start_time, end_time, duration) VALUES (p_trip_num, v_old_time, p_new_time, (p_new_time - v_old_time));
  37.              --DELETE FROM rcc_ptimestamp
  38.              DELETE FROM rcc_ptimestamp WHERE trip_number = p_trip_num AND start_time = v_old_time;
  39.         END IF;
  40.     END IF;
  41. END@
  42.  
  43.  
  44. CREATE TRIGGER rcc_ptimestamp AFTER INSERT ON trstat
  45. REFERENCING 
  46.     NEW AS NEW
  47. FOR EACH ROW MODE DB2SQL 
  48.  
  49. BEGIN ATOMIC    
  50.     CALL rcc_proc_ptimestamp(NEW.code, NEW.time_changed, NEW.trip_9, NEW.status);
  51. END@
  52.  
  53. --#SET TERMINATOR ;
  54.  
Aug 14 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.