How to compile invalid trigger in Oracle?
Trigger Compilation Methods:
We can compile Triggers through many ways.
Identifying the list of Triggers which are Invalid:
To identify uncompiled Triggers we have to query USER_OBJECTS data dictionary view.
Identifying complete list of Trigger in current schema.
--How to compile invalid trigger in Oracle?: Query SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER';
Identifying complete list of invalid Triggers in current schema.
--How to compile invalid trigger in Oracle? : Query to check the Invalid Triggers SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
So as per above data there is no invalid Trigger exists in current schema.
Creating setup for Trigger Invalidation testing.
--How to compile invalid trigger in Oracle? : Setup creation
CREATE TABLE EMPLOYEES_INV_TRIG_TEST
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; --How to compile invalid trigger in Oracle? : Setup creation
CREATE TABLE EMPLOYEES_UPDATED_TAB
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE 1 = 2; --How to compile invalid trigger in Oracle? : Setup creation CREATE OR REPLACE TRIGGER TRIG_EMP_UPD AFTER UPDATE ON EMPLOYEES_INV_TRIG_TEST FOR EACH ROW BEGIN INSERT INTO EMPLOYEES_UPDATED_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY) VALUES(:NEW.EMPLOYEE_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.SALARY); END; /
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER';
Individual Trigger Compilation through manual approach:
DROP TABLE EMPLOYEES_UPDATED_TAB;
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
Now if create the table EMPLOYEES_UPDATED_TAB then also the Trigger TRIG_EMP_UPD will not be compiled.
CREATE TABLE EMPLOYEES_UPDATED_TAB
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE 1 = 2; SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
ALTER TRIGGER TRIG_EMP_UPD COMPILE;
All Trigger Compilation through manual approach:
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
Creating custom script for all invalid Trigger compilation.
SELECT 'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;' SCRIPTS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
Creating PLSQL Block to compile all invalid Triggers:
BEGIN
FOR I IN (SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
AND STATUS = 'INVALID')
LOOP
EXECUTE IMMEDIATE 'ALTER TRIGGER '||I.OBJECT_NAME||' COMPILE';
END LOOP;
END;
/ SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER';
Individual Trigger Compilation through DBMS_DDL package:
EXEC DBMS_DDL.ALTER_COMPILE('TRIGGER', 'HR', 'TRIG_EMP_UPD');
EXEC DBMS_DDL.ALTER_COMPILE('TRIGGER', 'HR', 'TRIG_RESTRICT_EMP_SAL'); All Trigger Compilation through DBMS_DDL package:
SELECT 'EXEC DBMS_DDL.ALTER_COMPILE('||'''TRIGGER'''||','||'''HR'''||','||''''||OBJECT_NAME||''');' SCRIPTS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
AND STATUS = 'INVALID'; Complete schema compilation:
EXEC DBMS_UTILITY.compile_schema(schema => 'HR', compile_all => false);