Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: trigger created with compilation errors

    After Creating the Trigger oracle indicated "Trigger created with compilation errors but after executing the Show errors
    sql>show errors oracle sys "There are no errors".But then when I tested the insert on the base table it says "the trigger is invalid. Any help suggestions? How do I create a valid Trigger?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Try with
    Code:
    SQL> SHOW ERROR TRIGGER trigger_name
    P.S.
    How do I create a valid trigger?
    Obviously, using valid syntax. As you didn't provide trigger code, it is impossible to suggest any other solution.

  3. #3
    Join Date
    May 2008
    Posts
    2

    How do i create a valid trigger with this sample code?

    ================================================== ====================
    CREATE TABLE TRANSACTIONS1 (serial_no VARCHAR2(40),description VARCHAR2(30),
    transaction VARCHAR2(20),container_no VARCHAR2(40),stock_id VARCHAR2(10),
    test_result VARCHAR2(10), seller_id varchar2(20),buyer_id VARCHAR2(20),
    amount NUMBER, entry_date DATE, exit_date DATE DEFAULT sysdate, return_date DATE);
    ================================================== ====================
    CREATE TABLE TRANSACTIONS1_COPY (o_serial_no VARCHAR2(40), o_description VARCHAR2(30),
    o_transaction VARCHAR2(20), o_container_no VARCHAR2(40), o_stock_id VARCHAR2(10),
    o_test_result VARCHAR2(15), o_seller_id VARCHAR2(20), o_buyer_id VARCHAR2(20),
    o_amount NUMBER ,o_entry_date DATE, o_exit_date DATE,o_return_date DATE,
    n_serial_no VARCHAR2(40), n_description VARCHAR2(30), n_transaction VARCHAR2(20),
    n_container_no VARCHAR2(40), n_stock_id VARCHAR2(10), n_test_result VARCHAR2(10), n_seller_id VARCHAR2(20),
    n_buyer_id VARCHAR2(20),n_amount NUMBER,n_entry_date DATE,n_exit_date DATE,
    n_return_date DATE, chng_by VARCHAR2(20), chng_when DATE );
    ================================================== ===================
    Both Tables created successfully
    ================================================== ====================

    CREATE OR REPLACE TRIGGER TRANSACTIONS1_TRIG
    AFTER INSERT OR UPDATE OR DELETE
    ON TRANSACTIONS1
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW

    DECLARE
    v_changetype VARCHAR2(1);
    BEGIN

    INSERT INTO TRANSACTIONS1_COPY
    (o_serial_no, o_description,o_transaction,o_container_no,o_stock _id,o_test_result,
    o_seller_id,o_buyer_id,o_amount,o_entry_date,o_exi t_date,o_return_date ,
    n_serial_no,n_description,n_transaction,n_containe r_no,n_stock_id,n_test_result,
    n_seller_id,n_buyer_id,n_amount,n_entry_date,n_exi t_date,n_return_date, chng_by, chng_when)
    VALUES
    (:OLD.serial_no, :OLD.description,:OLD.transaction,:OLD.container_n o,:OLD.stock_id,:OLD.test_result,
    :OLD.seller_id,:OLD.buyer_id,:OLD.amount,:OLD.entr y_date,OLD.exit_date,:OLD.return.date,
    :NEW.serial_no, :NEW.description, :NEW.transaction,:NEW.container_no,:NEW.stock_no,: NEW.test_result,
    :NEW.seller_id,:NEW.buyer_id,:NEW.amount,:NEW.entr y_date,:NEW.exit_date,NEW.return_date,seller_id, SYSDATE);
    END;
    ================================================== =======
    INSERT STATEMENT ON TRANSACTIONS1
    ================================================== =======
    INSERT INTO TRANSACTIONS1
    VALUES
    ('&serial_no' ,'&description' ,
    '&transaction' ,'&container_no' ,'&stock_id' ,
    '&test_result' , '&seller_id' ,'&buyer_id',
    &amount, '&entry_date', '&exit_date', '&return_date','&chng_by','&chng_when' )
    ================================================== ====
    ORACLE SAYS 'INVALID TRIGGER'

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORACLE SAYS 'INVALID TRIGGER'
    Is your Caps Lock button broken & stuck on?
    I suspect Oracle says more & different than line above.

    It would help all concerned if you used CUT & PASTE to show the whole session.

    Read #1 STICKY post at top of the forum to learn how to use <code tags>.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2010
    Posts
    6
    created the indexes as follows with the explain plan underneath. I just used set autotrace traceonly
    set timing on


    HTML Code:
    set autotrace traceonly
    set timing on
    create index mer_well_data_idx
    on well_data (meridian);
    
    create index town_well_data_idx
    on well_data (township);
    
    create index range_well_data_idx
    on well_data (range);
    
    create index section_well_data_idx
    on well_data (section);
    
    create index mer_prod_data_idx
    on prod_data (meridian);
    
    create index town_prod_data_idx
    on prod_data (township);
    
    create index range_prod_data_idx
    on prod_data (range);
    
    create index section_prod_data_idx
    on prod_data (section);
    
    HTML Code:
    SQL> SELECT
      2  w.meridian,
      3  	w.township,
      4  	w.range,
      5  	w.section,
      6  	oil_prod
      7  FROM well_data w,
      8  	prod_data p
      9  WHERE depth BETWEEN 1000 and 4000
     10  AND w.meridian=p.meridian
     11  AND w.township=p.township
     12  AND w.range=p.range
     13  AND w.section=p.section
     14  ORDER BY w.meridian, w.township, w.range, w.section;
    
    4336847 rows selected.
    
    Elapsed: 00:00:43.98
    
    Execution Plan
    ----------------------------------------------------------                      
    Plan hash value: 234140215                                                      
                                                                                    
    --------------------------------------------------------------------------------
    ---------                                                                       
                                                                                    
    | Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| T
    ime     |                                                                       
                                                                                    
    --------------------------------------------------------------------------------
    ---------                                                                       
                                                                                    
    |   0 | SELECT STATEMENT    |           |  7373 |   201K|       |  8681   (4)| 0
    0:01:45 |                                                                       
                                                                                    
    |   1 |  SORT ORDER BY      |           |  7373 |   201K|   648K|  8681   (4)| 0
    0:01:45 |                                                                       
                                                                                    
    |*  2 |   HASH JOIN         |           |  7373 |   201K|       |  8619   (4)| 0
    0:01:44 |                                                                       
                                                                                    
    |*  3 |    TABLE ACCESS FULL| WELL_DATA |   860 | 12040 |       |     4   (0)| 0
    0:00:01 |                                                                       
                                                                                    
    |   4 |    TABLE ACCESS FULL| PROD_DATA |  4980K|    66M|       |  8531   (3)| 0
    0:01:43 |                                                                       
                                                                                    
    --------------------------------------------------------------------------------
    ---------                                                                       
                                                                                    
                                                                                    
    Predicate Information (identified by operation id):                             
    ---------------------------------------------------                             
                                                                                    
       2 - access("W"."MERIDIAN"="P"."MERIDIAN" AND "W"."TOWNSHIP"="P"."TOWNSHIP"   
                  AND "W"."RANGE"="P"."RANGE" AND "W"."SECTION"="P"."SECTION")      
       3 - filter("DEPTH"<=4000 AND "DEPTH">=1000)                                  
    
    
    Statistics
    ----------------------------------------------------------                      
            118  recursive calls                                                    
              8  db block gets                                                      
          30617  consistent gets                                                    
          15052  physical reads                                                     
              0  redo size                                                          
       75728370  bytes sent via SQL*Net to client                                   
        3180737  bytes received via SQL*Net from client                             
         289125  SQL*Net roundtrips to/from client                                  
              0  sorts (memory)                                                     
              1  sorts (disk)                                                       
        4336847  rows processed                                                     
    
    SQL> 
    SQL> spool off;

  6. #6
    Join Date
    Dec 2010
    Posts
    6

    Albin Ablignar

    HTML Code:
    SQL> select * from v$version;
    
    BANNER                                                                          
    ----------------------------------------------------------------                
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product                
    PL/SQL Release 10.2.0.1.0 - Production                                          
    CORE	10.2.0.1.0	Production                                                      
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                         
    NLSRTL Version 10.2.0.1.0 - Production                                          
    
    Elapsed: 00:00:00.00
    SQL> spool off

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please reply to the thread you started & not this one which has *NO* relevance to your problem.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •