problem with the trigger

[manjunathk]

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

[jmodic]

After runing triger creation script isue (in SQL*Plus): SHOW ERRORS TRIGGER DTR_TRG1. This will tell you what exactly is the reason for the trigger creation error.

Mine guess is your user who is creating the trigger does not have sufficient privileges either on both tables (if they are owned by another user) or on DBMS_OUTPUT package. The privileges must be granted directly to this user, not through role. Try granting him SELECT ANY TABLE and EXECUTE ANY PROCEDURE explicitly and then try to recreate the trigger.

[manjunathk]

Thanks jmodic,
When i ask to show errors after trigger creation, i get the following:

No errors for TRIGGER DTR_TRG1

the User creating the trigger is also the owner of both the tables. And the user also has execute any procedure privilege.

Thanks
manjunath

[portal]

hi
what i guess is by ur error message ur trigger is invalid
check ur script again for any spaces fix it then drop the trigger
and recreate it without errors-or probably some other user also owns the same trigger--that also goes for invalidation
-so try to drop it and recreate without errors and check whether it is valid(enabled).