OraFAQ Forum: SQL & PL/SQL » What is wrong with this trigger?

Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this trigger? (Oracle, 10g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

What is wrong with this trigger? [message #625993]

Fri, 17 October 2014 15:17

wzfoto
Messages: 2
Registered: October 2014
Location: NYC

Junior Member

I am trying to create a trigger that updates another table with the current system time when insert/update/delete are performed on ABC_TABLE. When I test it by doing insert into ABC_TABLE, the trigger gets executed and result in an ORA-04098 error code. I ran the update statement inside the trigger and it was fine. I am lost as to why I keep getting invalid trigger error. Does anyone have any ideas on how to debug this?

create or replace trigger log_last_update_1
after insert or update or delete on ABC_TABLE
begin
update LAST_UPDATE set update_time = sysdate where table_name='ABC_TABLE';
end;

Report message to a moderator

Re: What is wrong with this trigger? [message #625994 is a reply to message #625993]

Fri, 17 October 2014 15:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

04098, 00000, "trigger '%s.%s' is invalid and failed re-validation"
// *Cause: A trigger was attempted to be retrieved for execution and was
//         found to be invalid.  This also means that compilation/authorization
//         failed for the trigger.
// *Action: Options are to resolve the compilation/authorization errors,
//         disable the trigger, or drop the trigger.

Report message to a moderator

Re: What is wrong with this trigger? [message #625995 is a reply to message #625993]

Fri, 17 October 2014 15:28

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-04098: trigger '%s.%s' is invalid and failed re-validation
 *Cause: A trigger was attempted to be retrieved for execution and was
         found to be invalid.  This also means that compilation/authorization
         failed for the trigger.
 *Action: Options are to resolve the compilation/authorization errors,
         disable the trigger, or drop the trigger.

Execute
set role none
then the UPDATE
What happens?

Report message to a moderator

Re: What is wrong with this trigger? [message #626042 is a reply to message #625995]

Mon, 20 October 2014 02:37

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can also query user_errors to see what the errors are.

Report message to a moderator

Re: What is wrong with this trigger? [message #626087 is a reply to message #626042]

Mon, 20 October 2014 08:50

wzfoto
Messages: 2
Registered: October 2014
Location: NYC

Junior Member

I found out what the problem is. There were some junk statements after my trigger definition. The system incorporated them into the trigger, which is why it was failing. Thanks for the all the help. I found out by querying the user_errors table.

Report message to a moderator

Previous Topic:	ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Next Topic:	CREATE TABLE AS vs CREATE TABLE then INSERT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:01:08 CDT 2024