we have created trigger as below and it compiled successfully.
CREATE OR REPLACE TRIGGER CTS_PR_SETUP_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_CTS_PR_SETUP
FOR EACH ROW
DECLARE
V_AUDIT_OPRID VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
IF INSERTING
THEN
INSERT INTO PS_AUDIT_CTSPRSTUP ALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.CTS_PR_TMPL_ID,:NEW.DESCR,:NEW.LAST_UPDATE_DTTM,:NEW.RESOURCE_CATEGORY,:NEW.RESOURCE_SUB_CAT,:NEW.ANALYSIS_TYPE,:NEW.JOURNAL_CLASS,:NEW.APPROVALS_LINK,:NEW.FLAG3,:NEW.FLAG4,:NEW.FLAG5,:NEW.FLAG6,:NEW.VERSION,:NEW.ACTIVITY_TYPE);
ELSE
IF DELETING THEN
INSERT INTO PS_AUDIT_CTSPRSTUP VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.CTS_PR_TMPL_ID,:OLD.DESCR,:OLD.LAST_UPDATE_DTTM,:OLD.RESOURCE_CATEGORY,:OLD.RESOURCE_SUB_CAT,:OLD.ANALYSIS_TYPE,:OLD.JOURNAL_CLASS,:OLD.APPROVALS_LINK,:OLD.FLAG3,:OLD.FLAG4,:OLD.FLAG5,:OLD.FLAG6,:OLD.VERSION,:OLD.ACTIVITY_TYPE);
ELSE
INSERT INTO PS_AUDIT_CTSPRSTUP VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.CTS_PR_TMPL_ID,:OLD.DESCR,:OLD.LAST_UPDATE_DTTM,:OLD.RESOURCE_CATEGORY,:OLD.RESOURCE_SUB_CAT,:OLD.ANALYSIS_TYPE,:OLD.JOURNAL_CLASS,:OLD.APPROVALS_LINK,:OLD.FLAG3,:OLD.FLAG4,:OLD.FLAG5,:OLD.FLAG6,:OLD.VERSION,:OLD.ACTIVITY_TYPE);
INSERT INTO PS_AUDIT_CTSPRSTUP VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.CTS_PR_TMPL_ID,:NEW.DESCR,:NEW.LAST_UPDATE_DTTM,:NEW.RESOURCE_CATEGORY,:NEW.RESOURCE_SUB_CAT,:NEW.ANALYSIS_TYPE,:NEW.JOURNAL_CLASS,:NEW.APPROVALS_LINK,:NEW.FLAG3,:NEW.FLAG4,:NEW.FLAG5,:NEW.FLAG6,:NEW.VERSION,:NEW.ACTIVITY_TYPE);
END IF;
END IF;
END CTS_PR_SETUP_TR;
/
while updating source table we are getting below error
Error report -
SQL Error: ORA-04098: trigger 'SYSADM.CTS_PR_SETUP_TR' is invalid and failed re-validation
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.
Please help me on this, what could be the issue.
Thanks,
Karthick