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

Products

• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

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 '<database id>.CA_TR_INS_OWN_RESOURCE_B4' no es válido y ha fallado al revalidar (is invalid and failed re-validation)

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 17.x 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).