Home » SQL & PL/SQL » SQL & PL/SQL » Why is TRIGGER "not firing"?
Why is TRIGGER "not firing"? [message #258869] Mon, 13 August 2007 12:28 Go to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I am having a brain cramp & need a 2nd pair of eyes to see the problem.
10:10:34 SQL> connect / as sysdba
Connected.
10:10:41 SQL> @audit-event-logons.sql
10:10:43 SQL> CREATE OR REPLACE TRIGGER AUDIT_EVENT_TRIGGER
10:10:43   2  AFTER LOGON ON DATABASE
10:10:43   3  DECLARE
10:10:43   4  MYSID NUMBER;
10:10:43   5  v_program  varchar2(128);
10:10:43   6  v_machine  varchar2(128);
10:10:43   7  BEGIN
10:10:43   8  if ( substr(USER,1,5) = 'EVENT' )
10:10:43   9  then
10:10:43  10     select dbms_support.MYSID into MYSID from dual;
10:10:43  11     select machine, program into v_machine, v_program from V$session where SID = MYSID;
10:10:43  12     insert into dbadmin.audit_event (username, logon_time, machine, program) values (user, sysdate, v_machine, v_program);
10:10:43  13     commit;
10:10:43  14  end if;
10:10:43  15  END;
10:10:43  16  /

Trigger created.

10:10:43 SQL> select count(*) from dbadmin.audit_event;

  COUNT(*)
----------
         0

10:14:54 SQL>  select count(*) from dbadmin.audit_event;

  COUNT(*)
----------
         0

10:15:27 SQL> select username, logon_time from v$session where machine = 'eg11.hitbox.com';

USERNAME                       LOGON_TIME
------------------------------ -------------------
EVENT_USER                     2007-08-13 10:15:17
EVENT_USER                     2007-08-13 10:15:20
EVENT_USER                     2007-08-13 10:15:19
EVENT_USER                     2007-08-13 10:15:17
EVENT_USER                     2007-08-13 10:15:17
EVENT_USER                     2007-08-13 10:15:17

6 rows selected.

10:15:54 SQL>  select count(*) from dbadmin.audit_event;

  COUNT(*)
----------
         0


10:16:07 SQL> connect event_user/password
Connected.
10:17:54 SQL> set role none;

Role set.

10:18:05 SQL> DECLARE
10:18:14   2  MYSID NUMBER;
10:18:14   3  v_program  varchar2(128);
10:18:14   4  v_machine  varchar2(128);
10:18:14   5  BEGIN
10:18:14   6  if ( substr(USER,1,5) = 'EVENT' )
10:18:14   7  then
10:18:14   8     select dbms_support.MYSID into MYSID from dual;
10:18:14   9     select machine, program into v_machine, v_program from V$session where SID = MYSID;
10:18:14  10     insert into dbadmin.audit_event (username, logon_time, machine, program) values (user, sysdate, v_machine, v_program);
10:18:14  11     commit;
10:18:14  12  end if;
10:18:14  13  END;
10:18:14  14  /

PL/SQL procedure successfully completed.

10:18:16 SQL>  select count(*) from dbadmin.audit_event;

  COUNT(*)
----------
         1

10:18:25 SQL> 

Previously I issued
GRANT EXECUTE on DBMS_SUPPORT TO EVENT_USER;
GRANT SELECT ON V_$SESSION TO EVENT_USER;
GRANT ALL ON DBADMIN.AUDIT_EVENT TO EVENT_USER;
As shown above when I login via SQL*Plus as EVENT_USER
& after SET ROLE NONE, the PL/SQL performs as expected.
Why doesn't the DBADMIN.AUDIT_EVENT table get a new record after the EVENT_USER logs into this database?
What am I overlooking?

[Updated on: Mon, 13 August 2007 12:29] by Moderator

Report message to a moderator

Re: Why is TRIGGER "not firing"? [message #258879 is a reply to message #258869] Mon, 13 August 2007 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
This is a real head scratcher for me.
As a sanity test, I installed the trigger in a different DB & it works there as expected & I did not do the GRANTs on the 2nd DB.
This smells like an error of ommission, I at the present I don't see what it could be.
Re: Why is TRIGGER "not firing"? [message #258881 is a reply to message #258869] Mon, 13 August 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version (4 decimals)?
I tried it many variations of what you posted but it always worked for me.

Regards
Michel
Re: Why is TRIGGER "not firing"? [message #258884 is a reply to message #258869] Mon, 13 August 2007 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member

  1* select trigger_name, TRIGGERING_EVENT from dba_triggers where TRIGGERING_EVENT like '%LOGON%'
11:03:11 SQL> /

TRIGGER_NAME
------------------------------
TRIGGERING_EVENT
------------------------------
AUDIT_EVENT_TRIGGER
LOGON

11:03:13 SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


It is working for me except on the 1 system where I REALLY need it to work.
FWIW - As you can see my "new" trigger is the only LOGON trigger in this DB.
I had hoped I had 2 LOGON triggers & the new one was being ignored.

[Updated on: Mon, 13 August 2007 13:20] by Moderator

Report message to a moderator

Re: Why is TRIGGER "not firing"? [message #258886 is a reply to message #258884] Mon, 13 August 2007 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure your trigger is enabled?

Regards
Michel
Re: Why is TRIGGER "not firing"? [message #258887 is a reply to message #258869] Mon, 13 August 2007 13:32 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
nice try but no cigar.
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 13 11:31:36 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from dba_triggers where trigger_name = 'AUDIT_EVENT_TRIGGER';

STATUS
--------
ENABLED

SQL> 

Re: Why is TRIGGER "not firing"? [message #258891 is a reply to message #258887] Mon, 13 August 2007 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if it is important but use SYS_CONTEXT('USERENV','SID') or v$mystat to get sid.
dbms_support should be used only with support autorisation and is not free of bugs.
Moreover all its functions are covered by other features/packages.

Regards
Michel
Re: Why is TRIGGER "not firing"? [message #258898 is a reply to message #258869] Mon, 13 August 2007 14:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
We have met the enemy & they is us.

SQL> select name, value from v$parameter where name like '%trig%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_system_trig_enabled
FALSE


12:16:51 SQL> select a.obj#, a.sys_evts, b.name from trigger$ a,obj$ b
12:16:52   2  where a.sys_evts > 0 and a.obj#=b.obj#; 


      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
      7332        128 AW_DROP_TRG
     10180        128 NO_VM_DROP
     10181        128 NO_VM_DROP_A
     10183         32 NO_VM_CREATE
     10185         64 NO_VM_ALTER
     37104          1 AURORA$SERVER$STARTUP
     37105          2 AURORA$SERVER$SHUTDOWN
     39493         64 CDC_ALTER_CTABLE_BEFORE
     39494         32 CDC_CREATE_CTABLE_AFTER
     39495         32 CDC_CREATE_CTABLE_BEFORE
     39496        128 CDC_DROP_CTABLE_BEFORE

      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
     40548        128 EXPFIL_DROPOBJ_MAINT
     40549        128 EXPFIL_DROPUSR_MAINT
     40550         96 EXPFIL_RESTRICT_TYPEEVOLVE
     40551       8256 EXPFIL_ALTEREXPTAB_MAINT
     42008       4224 XDB_PI_TRIG
     42575       4096 RLMGR_TRUNCATE_MAINT
     45913        128 SDO_DROP_USER
     45914        128 SDO_DROP_USER_BEFORE
     46019        128 SDO_TOPO_DROP_FTBL
     46745          1 OLAPISTARTUPTRIGGER
     46746          2 OLAPISHUTDOWNTRIGGER

      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
     49504        128 SDO_GEOR_DROP_USER
     49505       4096 SDO_GEOR_TRUNC_TABLE
     49569        128 SDO_NETWORK_DROP_USER
     49581        128 SDO_NETWORK_CONS_DROP_TRIG
     49591        128 SDO_NETWORK_UD_DROP_TRIG
   4985554          8 MY_LOGON

28 rows selected.

Subject: How to find triggers being disabled with _SYSTEM_TRIG_ENABLED parameter
Doc ID: Note:244524.1

alter system set "_system_trig_enabled"=TRUE scope=both;

and now my trigger WORKS!
Re: Why is TRIGGER "not firing"? [message #258909 is a reply to message #258898] Mon, 13 August 2007 15:02 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! should have think about this as you created your trigger as SYS.

Thanks for the feedback.
Regards
Michel
Previous Topic: Is there a reverse method?
Next Topic: ISSUE
Goto Forum:
  


Current Time: Sat Dec 03 15:48:23 CST 2016

Total time taken to generate the page: 0.05664 seconds