Database or Logon Event Trigger becomes Invalid: Who can Connect? (Doc ID 120712.1)

Last updated on DECEMBER 11, 2023

Applies to:

Symptoms

Problem Description
-------------------

You create a database event trigger that fires each time a user
connects to the database.  
For any reason, the login trigger becomes invalid.  (The trigger is 
retrieving information from a table that may be inadvertently dropped.)
 
Who can connect?

Example
-------

*** The following trigger selects information from the LIST table and
    inserts the selected rows into the LIST_OLD table

SQL> CREATE OR REPLACE TRIGGER sys.trig_select
  2  AFTER LOGON ON DATABASE
  3  DECLARE
  4     nam varchar2(16);
  5     years_old number(3);
  6     cursor list_names is
  7     select * from test.list where age>30;
  8    BEGIN
  9     open list_names;
 10     loop
 11     fetch list_names into nam,years_old;
 12     exit when list_names%notfound;
 13     insert into  test.list_old values (nam,years_old);
 14     end loop;
 15    END;
 16  /

Trigger created.

SQL> truncate table test.list_old;
Table truncated.

$ more login.sql
set serveroutput on
select * from test.list_old;

*** The TEST user connection fires the trigger, selects 2 rows
    from the LIST table, inserts these 2 rows into the LIST_OLD table
    displayed by the SELECT statement from the login.sql script
    automatically fired after the connection => 2 rows in LIST_OLD
 
$sqlplus test/test
SQL*Plus: Release 8.1.6.0.0 - Production on Wed Oct 18 12:18:48 2000

NAME                    AGE
---------------- ----------
LEGER Anne               35
BOURE Christian          37

*** The SYSTEM user connection fires the trigger, selects 2 rows
    from the LIST table, inserts these 2 rows into the LIST_OLD table
    displayed by the SELECT statement from the login.sql script
    automatically fired after the connection => 4 rows in LIST_OLD

$sqlplus system/manager
SQL*Plus: Release 8.1.6.0.0 - Production on Wed Oct 18 12:19:14 2000

NAME                    AGE
---------------- ----------
LEGER Anne               35
BOURE Christian          37
LEGER Anne               35
BOURE Christian          37

*** The LIST table is dropped => the trigger becomes invalid

SQL> DROP TABLE test.list;
Table dropped.

*** The TEST user connection is not possible anymore

$sqlplus test/test

SQL*Plus: Release 8.1.6.0.0 - Production on Wed Oct 18 12:21:14 2000
(c) Copyright 1999 Oracle Corporation.  All rights reserved.

ERROR:
ORA-04098: trigger 'SYS.LOGON_SELECT' is invalid and failed re-validation


Enter user-name: system/manager

Connected to:
NAME                    AGE
---------------- ----------
LEGER Anne               35
BOURE Christian          37
LEGER Anne               35
BOURE Christian          37

SQL> connect user1/user1
ERROR:
ORA-04098: trigger 'SYS.LOGON_SELECT' is invalid and failed re-validation


Warning: You are no longer connected to ORACLE.

Changes

Cause