Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Invalid Triggers

Hello!
We have an Oracle Database 11g Release and some days ago we found a problem with some data of a table because a trigger of the table wasn't executed due to being Invalid. There weren't any errors in the code of the trigger.
I have read that when the trigger is going to be executed, Oracle automatically notices that its status is Invalid and recompile it. So, I doubt why it didn't happen in our case, and if there is an automatic way to avoid this case.
I have found some ways like using "utlrp.sql" or "alter trigger....compile" but to do that we should create some jobs or include that code in every trigger or code. I wonder if there is any automatic code.
Thanks a lot!
Answers
-
Triggers don't go invalid just like that.
If they go invalid just like that, the reason is in your code.
There is no reason to set up jobs to automatically recompile and
this community will not be able to resolve your problem without looking at your code.
-----------
Sybrand Bakker
Senior Oracle DBA
-
There are several reasons why a block of code might go invalid, but Oracle will recompile the code if it can so the code will execute. If, for whatever reason, the code cannot be recompiled, then Oracle will throw an error:
SQL> create table t (id number, descr varchar2(20)); Table created. SQL> create table t_hist (id number, descr varchar2(20)); Table created. SQL> insert into t values (1, 'One'); 1 row created. SQL> commit; Commit complete. SQL> create trigger t_bu 2 before update on t 3 for each row 4 begin 5 insert into t_hist values (:new.id, : old.descr); 6 end; 7 / Trigger created. SQL> select object_name, status 2 from user_objects 3 where object_name = 'T_BU' and 4 object_type = 'TRIGGER'; OBJECT_NAME STATUS --------------- ------- T_BU VALID SQL> update t 2 set descr = 'Un' 3 where id = 1; 1 row updated. SQL> commit; Commit complete. SQL> select * from t_hist; ID DESCR ---------- -------------------- 1 One SQL> alter table t_hist add (udp_dt date); Table altered. SQL> select object_name, status 2 from user_objects 3 where object_name = 'T_BU' and 4 object_type = 'TRIGGER'; OBJECT_NAME STATUS --------------- ------- T_BU INVALID SQL> update t 2 set descr = 'One' 3 where id = 1; update t * ERROR at line 1: ORA-04098: trigger 'OPS$ORACLE.T_BU' is invalid and failed re-validation
So, either the trigger did try to execute but failed to recompile and the code that executed the triggering event did not report the error (a poor when others handler spring to mind), or the trigger did successfully execute and the trigger code does not actually do what you want it to do, at least in some circumstances.
John
-
we found a problem with some data of a table because a trigger of the table wasn't executed due to being Invalid.
At this point in your post, I become suspicious. If your trigger was invalid and could not be made valid, then any DML operation that would cause the trigger to fire would not work. So your data should not have a problem. DML operations would have a problem in that they won't be allowed, but the data in the table won't have a problem. . As proof, I'll offer a simple example:
-- Create a test table.
SQL> create table test_tab (id number);
Table created.
-- create a trigger that will purposely be invalid. I cant insert SYSDATE into a NUMBER datatype.
SQL> create trigger test_tab_trig
2 before insert on test_tab
3 declare
4 v_numbers number;
5 begin
6 select sysdate into v_numbers from dual;
7 end;
8 /
Warning: Trigger created with compilation errors.
-- Verify trigger is invalid
SQL> select status from dba_objects where object_name='TEST_TAB_TRIG';
STATUS
-------
INVALID
-- Attempt to insert a value into the table.
SQL> insert into test_tab values (10);
insert into test_tab values (10)
*
ERROR at line 1:
ORA-04098: trigger 'PEASLAND.TEST_TAB_TRIG' is invalid and failed re-validation
-- The insert failed and the data is not in the table.
SQL> select * From test_tab;
no rows selected
So how does your data have a problem if the trigger is invalid? The data in the table still conforms to the trigger's rules and new data is not allowed in.
I have read that when the trigger is going to be executed, Oracle automatically notices that its status is Invalid and recompile it.
That is true. Oracle will attempt to recompile it. However, there is probably an underlying reason why the trigger is invalid. In my example above, I have bad code. Oracle can't fix it and a recompile will still result in an invalid trigger. If the recompile works, then Oracle goes on its merry way. If the recompile fails, the trigger will still be invalid and the DML that caused the trigger to fire will fail.
I wouldn't waste my energies on an automated routine to recompile triggers.
Cheers,
Brian -
Hi,
think you have to check trigger status and look for messages in alert log.
This behavior is not normal as indicate BPeaslandDBA.
Regards.
Willy