8 Replies Latest reply: Apr 16, 2014 6:10 AM by user123456789 RSS

    When clause missing from logon trigger

    user123456789

      I have an issues which seems very similar to Bug 14840619 - FORUM - TRIGGER WHEN-CLAUSE NOT SHOWN IF USER ONLY HAS BASIC PRIVILEGES

       

      Assuming SCOTT is a DBA user and he issues the following command to create a logon trigger.

       

      CREATE OR REPLACE TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" AFTER LOGON ON DATABASE

      WHEN (USER in ('SCOTT')) BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

      END;

      /

      ALTER TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" DISABLE; 

       

      The trigger is created successfully.

       

      When viewing the trigger code from sql developer I don't see the WHEN clause.

       

      Im using sql developer version 4.0.1.14 (Build Main 14.48) 64 bit on Windows 7

       

      Is this a known bug?

       

      Thank you

        • 1. Re: When clause missing from logon trigger
          Jeff Smith Sqldev Pm-Oracle

          No, and I'm not able to reproduce the behavior.

           

          what do you get if you run the dbms_metadata call to generate the DDL?

           

          select dbms_metadata.get_ddl('TRIGGER', 'SCOTT_LOGON_TRIGGER') from dual;

          • 2. Re: When clause missing from logon trigger
            user123456789

            dbms_metadata.get_ddl gives me the correct information.

             

            CREATE OR REPLACE TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" AFTER LOGON ON DATABASE

            WHEN (USER in ('SCOTT')) BEGIN

            EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

            END;

            /

            ALTER TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" DISABLE;

             

            set long 1000

            select dbms_metadata.get_ddl('TRIGGER', 'SCOTT_LOGON_TRG', 'SYSTEM') from dual;

             

            Output

             

            TRIGGER SCOTT_LOGON_TRG compiled

            trigger "SYSTEM"."SCOTT_LOGON_TRG" altered.

            DBMS_METADATA.GET_DDL('TRIGGER','SCOTT_LOGON_TRG','SYSTEM')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             

              CREATE OR REPLACE TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" AFTER LOGON ON DATABASE

            WHEN (USER in ('SCOTT')) BEGIN

            EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

            END;

            ALTER TRIGGER "SYSTEM"."SCOTT_LOGON_TRG" DISABLE

             

             

            But when I go to "Other Users" -> System -> Triggers -> SCOTT_LOGON_TRG I get the following (missing WHEN (USER in ('SCOTT')))

             

            create or replace trigger "SYSTEM"."SCOTT_LOGON_TRG" AFTER LOGON ON DATABASE

            BEGIN

            EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

            END;

            • 3. Re: When clause missing from logon trigger
              Jeff Smith Sqldev Pm-Oracle

              Other Users...

               

              If you're logged in as SYSTEM, does it show correctly?

              • 4. Re: When clause missing from logon trigger
                user123456789

                Jeff,

                 

                I never said I was logged in as SYSTEM.  I said "Assuming SCOTT is a DBA user and he issues..." So I'm logged in as SCOTT the DBA.  If you were testing this using the SYSTEM account then that explains why you could not reproduce the error. This is a case where SCOTT the dba creates a trigger in another schema (in this case the SYSTEM) and then attempt to view the newly created trigger via the interface.

                 

                Could you try again using another DBA account other than SYSTEM to see if you can reproduce the error.

                 

                Thank you.

                • 5. Re: When clause missing from logon trigger
                  Jeff Smith Sqldev Pm-Oracle

                  >>When viewing the trigger code from sql developer I don't see the WHEN clause.

                  Right, you never said what user was viewing the trigger.

                   

                  I'm using HR for testing, and I"m still not seeing the issue.

                   

                  If you think it's related to that bug then you should open an SR. However, your DBA account wouldn't have limited privs, so I'm not sure it would apply.

                  • 6. Re: When clause missing from logon trigger
                    rp0428
                    I never said I was logged in as SYSTEM.

                    Jeff NEVER said you were logged in as SYSTEM. Reread, and then answer, his question:

                    If you're logged in as SYSTEM, does it show correctly?

                    The answer to that helps narrow down what the issue might be.

                    Could you try again using another DBA account other than SYSTEM to see if you can reproduce the error.

                    Better - could YOU try again and use SYSTEM to see if YOU can reproduce the error?

                    • 7. Re: When clause missing from logon trigger
                      user123456789

                      Yes, if logged in as SYSTEM the trigger is shown correctly.

                       

                      Also if I enable trace on my SCOTT the DBA session I see the following query being executed to get the source.

                       

                      select text prefix, null trigger_body from all_source

                      where name=:NAME

                      and owner=:OWNER

                      and USER = :OWNER

                      union all --disjoint

                      select 'trigger '||description, trigger_body from all_triggers

                      where /*vacuous bind to match*/:TYPE='TRIGGER' and owner = :OWNER and trigger_name = :NAME  and USER != :OWNER

                       

                      When converting the bind variables I get the following

                       

                      select text prefix, null trigger_body from all_source

                      where name='SCOTT_LOGON_TRG'

                      and owner='SYSTEM'

                      and USER = 'SYSTEM'

                      union all --disjoint

                      select 'trigger '||description, trigger_body from all_triggers

                      where /*vacuous bind to match*/'TRIGGER'='TRIGGER' and owner = 'SYSTEM' and trigger_name = 'SCOTT_LOGON_TRG'  and USER != 'SYSTEM'

                       

                       

                      PREFIX                                                             TRIGGER_BODY                                                                            
                      ------------------------------------------------------------------ ---------------------------------------------------------------------------------------------
                      trigger "SYSTEM"."SCOTT_LOGON_TRG" AFTER LOGON ON DATABASE         BEGIN                                                                                   
                      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''; 
                      END;                                                                                    
                      • 8. Re: When clause missing from logon trigger
                        user123456789

                        I have created a service request with Oracle support and they were able to reproduce the issues.

                         

                        Here is the related bug number

                         

                        Bug 18603106 : WHEN CLAUSE MISSING FROM LOGON TRIGGER