开发者

Oracle trigger failed -ORA-04098

开发者 https://www.devze.com 2022-12-21 01:34 出处:网络
I have a table for which i have written a trigger: CREATE OR REPLACE TRIGGER ac01_control_trigg AFTER INSERT ON ac1_control_test

I have a table for which i have written a trigger:

CREATE OR REPLACE TRIGGER ac01_control_trigg
AFTER INSERT ON ac1_control_test 
FOR EACH ROW
DECLARE
    BEGIN
   IF :NEW.cur_pgm_name = 'LSN' 
   AND :NEW.nxt_pgm_name ='MD' 
   AND :NEW.file_status='RD' THEN 
    INSERT INTO ac1_control_test 
    (FILE_NAME, FILE_PATH,FILE_STATUS,CUR_PGM_NAME,NXT_PGM_NAME)
    VALUES 
   (:NEW.FILE_NAME, :NEW.FILE_PATH,:NEW.FILE_STATUS,:NEW.CUR_PGM_NAME,'MD_MPS');   
END IF;
END ac01_control_trigg;

when i am trying to insert into the table i am getting an error below!

ORA-04098: trigger 'CN开发者_如何学运维GDB18.AC01_CONTROL_TRIGG' is invalid and failed re-validation

could anybody please help?

also when i compile the trigger in Toad,i am getting compile errors as below:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/65     PLS-00049: bad bind variable 'NEW_FILE_STATUS'

but what is the wrong with this? and what does this error mean?


EDIT: Now that we see the message, the solution is easy :)

Use
:NEW.file_status='RD' instead of
:new_file_status='RD'


Your trigger object is invalid (there is a problem with the code).

Test this with:

SELECT object_name, status
FROM user_objects
WHERE object_name = 'AC1_CONTROL_TRIGG';

Should return:
AC1_CONTROL_TRIGG INVALID

You can try the following in SQL*Plus to get a description of the error:

ALTER TRIGGER ac1_control_trigg COMPILE;

SHOW ERROR TRIGGER ac1_control_trigg;

Using TOAD, you can just type these two lines into an editor, select them and use Editor>Execute SQL via SQL*Plus.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号