Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers:
-- connect to DB connect myschema@TESTDB -- Create table create table myschema.t as select * from all_objects; -- Create before insert create or replace trigger myschema.trg before insert on t for each row begin :new.object_id :=12; end; / -- Grant insert privilege to table grant insert on myschema.t to otherschema; -- Modify underlying table to make trigger go invalid alter table myschema.t drop column owner; -- Connect as otherschema connect otherschema@TESTDB -- View status of trigger (invalid) select object_name, status from dba_objects where owner = 'MYSCHEMA'; OBJECT_NAME STATUS ----------- ------- TRG INVALID -- insert record into myschema.t insert into myschema.t select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME, TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY, NAMESPACE,EDITION_NAME from all_objects where rownum=1; -- View status of trigger (valid) select object_name, status from dba_objects where owner = 'MYSCHEMA'; OBJECT_NAME STATUS ----------- ------- TRG VALID
All of that is as expected... now lets look at a logon trigger:
-- Connect as privileged schema connect mydba@TESTDB -- Create table in privileged schema create table mydba.test_table ( c1 varchar2(100), c2 date ); -- Create logon trigger create or replace trigger mydba.test_logon_trg after logon on database declare v_variable varchar2(100); begin select c1 into v_variable from mydba.test_table; exception when no_data_found then null; end; / -- Modify table to make trigger go invalid alter table mydba.test_table modify ( c1 varchar2(200) ); -- Check status of trigger ( invalid ) select owner, object_name, status from dba_objects where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG'; -- Connect as non-privileged user (if you use a privileged, i.e. DBA, account it bypasses the trigger) connect myschema@TESTDB ORA-04045: errors during recompilation/revalidation of MYDBA.TEST_LOGON_TRG ORA-01031: insufficient privileges
It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.