Showing results for 
Search instead for 
Do you mean 

ORA-20108: triggering event 'INSERT OR UPDATE' not allowed returned from GTMAdmin

by Moderator on ‎07-22-2016 01:29 PM (1,256 Views)

Symptoms

While trying to secure a table in Oracle using the GTM Administrator tool, the following error occurs:

 

ERROR at line 1:
ORA-20108: triggering event 'INSERT OR UPDATE' not allowed
ORA-06512: at "SYS.LTDDL", line 517
ORA-06512: at "SYS.LTDDL", line 1273
ORA-06512: at "SYS.LTDDL", line 1259
ORA-06512: at "SYS.LT", line 8383
ORA-06512: at line 1

 

Diagnosis

This usually indicates there is a trigger that violates the rules set down by Oracle Workspace Manager. In most cases, it means that trigger contains more than one DML event clause (INSERT, UPDATE, DELETE) in the BEFORE or AFTER section, the following trigger for example contains both an INSERT and UPDATE clause which will cause the ORA-20108 error:

 

Create Or Replace Trigger MY_TRIGGER After Insert Or Update On PROJECT
For Each Row
Declare
Begin
Insert into LOG_TABLE (ID)
Values (:new.pjt_id);
End;
/

 

Solution

The following steps should correct the problem:

Drop the original trigger
Re-create separate triggers with identical bodies for each of the DML event clauses that were used in the former trigger. Eg. if the original trigger had an AFTER INSERT OR UPDATE clause, create two separate triggers one with AFTER INSERT and the other with an AFTER UPDATE clause both having the same trigger bodies.

For example, the above trigger could be replaced with the following two triggers:

 

Create Or Replace Trigger TRG_i_PROJECT
After Insert On PROJECT
For Each Row
Declare
Begin
Insert into LOG_TABLE (ID )
Values (:new.pjt_id);
End;
/

Create Or Replace Triggger TRG_U_PROJECT
After Update On PROJECT
For Each Row
Declare
Begin
Insert into LOG_TABLE (ID)
Values (:new.pjt_id);
End;
/


Version enable the table now. It should succeed without errors.

Optionally the user can check the status of the triggers in the USER_WM_TAB_TRIGGERS table.

Contributors