Hi Friends,
I am getting the following error when trying to insert the data manually.
ORA-04098: trigger 'DTR_TRG1' is invalid and failed re-validation


I went ahead and tried doing the loading anyway using SQL *Loader and then I got the following error:

SQL*Loader: Release 8.0.5.0.0 - Production on Tue Jan 9 13:58:15 2001

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Control File: E:/ctrl_file_ex.txt
Data File: E:/ctrl_file_ex.txt
Bad File: E:/ctrl_file_ex.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table TABLE_A, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL_A FIRST * , CHARACTER
COL_C NEXT * , CHARACTER
COL_D NEXT * , CHARACTER

SQL*Loader-929: Error parsing insert statement for table TABLE_A.
ORA-04098: trigger 'DTR_TRG1' is invalid and failed re-validation

The trigger script, and the table structure with the data inserted into table_B are as follows:


Trigger:

create or replace trigger dtr_trg1 before insert on table_A for each row
declare
begin
select col_e into :new.col_b from table_B where col_d = :new.col_d;

exception
when others then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
/


Control File:

load data
infile *
append
into table table_A
FIELDS TERMINATED BY ","
(
col_a,
col_c,
col_d
)
begindata
10,,101,1,
11,,102,2,
12,,103,3

***************************************

table_A
col_a col_b col_c col_d(All 4 fields are Number datatype)


table_B
col_d col_e col_f(All 3 fields are Number datatype)
1 51 150
2 52 151
3 53 152


Can somebody help me what may be the problem?
Thanks and regards
manjunath