Unable to create Configuration Item (CI) due to Oracle ORA-04098 ERROR for invalid trigger CA_TR_INS_OWN_RESOURCE.

Document ID : KB000008968
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

When attempting to create a new Configuration Item (CI), the CI does not get created.  A search for the CI gives no match; it cannot be found.

ERROR messages are written to the stdlog during the attempt to create the CI.  One error message indicates that the trigger named "CA_TR_INS_OWN_RESOURCE_B4" is invalid and failed to re-compile/re-validate. The trigger must run successfully before a new CI can be inserted into the mdb database.

A sample of the ERROR messages is:

sqlagt:upinde 6204 ERROR orclclass.c 1302 SQL Statement Failed. ORA: 4098 Message: ORA-04098: el disparador 'MDBADMIN.CA_TR_INS_OWN_RESOURCE_B4' no es válido y ha fallado al revalidar 

sqlagt:upinde 6204 ERROR orclclass.c 1303 Clause (INSERT INTO ca_owned_resource ...

 

Even if you attempt to recompile the trigger using the following SQL command, the problem may not be resolved:

ALTER TRIGGER MDBADMIN.CA_TR_INS_OWN_RESOURCE_B4 COMPILE

 

Environment:
CA Service Desk Manager with the Oracle mdb database
Cause:

Run the following SQL command to get more information about the error:

select * from user_errors where type = 'TRIGGER' and name = 'CA_TR_INS_OWN_RESOURCE_B4'

If you receive the following results:

NAME TYPE SEQUENCE LINE POSITION 
------------------------------ ------------ ---------- ---------- ---------- 
TEXT 
-------------------------------------------------------------------------------- 

ATTRIBUTE MESSAGE_NUMBER 
--------- -------------- 
CA_TR_INS_OWN_RESOURCE_B4 TRIGGER 1 2 6 
PLS-00049: bad bind variable 'NEW.OWN_RESOURCE_ID' 
ERROR 49 

CA_TR_INS_OWN_RESOURCE_B4 TRIGGER 2 3 5 
PLS-00049: bad bind variable 'NEW.OWN_RESOURCE_ID' 
ERROR 49 

NAME TYPE SEQUENCE LINE POSITION 
------------------------------ ------------ ---------- ---------- ---------- 
TEXT 
-------------------------------------------------------------------------------- 

ATTRIBUTE MESSAGE_NUMBER 
--------- -------------- 

SQL> 

then, run the SQL command:

DESCRIBE CA_OWNED_RESOURCE;

and check the output to confirm whether or not the column named "OWN_RESOURCE_ID", is missing from the table named "CA_OWNED_RESOURCE".

Resolution:

After confirming that OWN_RESOURCE_ID is missing from CA_OWNED_RESOURCE, follow these steps:

  1. Run the following SQL to create the missing column:


    ALTER TABLE CA_OWNED_RESOURCE ADD OWN_RESOURCE_ID INTEGER NULL 
  2. Use the CREATE INDEX command to create the index named CA_OWNED_RESOURCE_IDX_47 on the OWN_RESOURCE_ID column.
  3. Recompile the trigger:

    ALTER TRIGGER MDBADMIN.CA_TR_INS_OWN_RESOURCE_B4 COMPILE 
  4. Confirm that you are able to create a new Configuration Item (CI).