Forum Stats

  • 3,838,105 Users
  • 2,262,332 Discussions
  • 7,900,506 Comments

Discussions

Trigger on logoff trouble

741904
741904 Member Posts: 31
edited Mar 3, 2010 5:36PM in SQL & PL/SQL
Hi to all,

I'd like to try setting a trigger on logoff event. But when I logoff either from the terminal and from the APEX's web igu admin nothing's added into the target table.

Any clue?
Thank.
Claude
------------------------------------------
The trigger code:
===========
CREATE OR REPLACE TRIGGER TrigLog
BEFORE LOGOFF ON CLASIE.SCHEMA
BEGIN
INSERT INTO TTRIG VALUES('Test');
END;

the table:
======
create table ttrig (message VARCHAR2(100));

No errors showned when creating both db objets.

My system:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Tagged:

Answers

  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    It is working perfectly in my laptop.

    Did you exit from your SQL prompt or How are you checking that?

    Regards.

    Satyaki De.
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    Are you connecting as CLASIE and then logging off.
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    What roles are granted to the user?
    DBA role perhaps?
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1844531724208
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    Martijn,

    The DBA role does not prevent the trigger been fired. So I think this is not the cause of the issue...

    rgds
    Lakmal...
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Hi Lakmal,

    Yes, you're right, thanks for pointing out, I was a bit too hasty with my reply.
    I ran some tests (which I should have done in the first place before posting ).
    SQL>  create table loginfo(msg varchar2(1000));
    
    Table created.
    
    SQL> create or replace trigger test
      2  before logoff on hoek.schema
      3  begin
      4  insert into loginfo values( user||' logged off @ '||to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss'));
      5  end;
      6  /
    
    Trigger created.
    
    SQL> disc
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    SQL> conn hoek/[email protected]
    Verbonden.
    SQL> select * from loginfo;
    
    MSG
    --------------------------------------------------------------------------------------------------------------------------
    HOEK logged off @ 03-03-2010 21:37:27
    
    1 row selected.
    
    SQL> disc
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    SQL> conn hr/[email protected]
    Verbonden.
    SQL> disc
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    SQL> conn hoek/[email protected]
    Verbonden.
    SQL> select * from loginfo;
    
    MSG
    --------------------------------------------------------------------------------------------------------------------------
    HOEK logged off @ 03-03-2010 21:37:27
    HOEK logged off @ 03-03-2010 21:37:57
    
    2 rows selected.
    
    SQL> disc
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    SQL> conn /@xe as sysdba
    Verbonden.
    SQL> select * from hoek.loginfo;
    
    MSG
    --------------------------------------------------------------------------------------------------------------------------
    HOEK logged off @ 03-03-2010 21:37:27
    HOEK logged off @ 03-03-2010 21:37:57
    HOEK logged off @ 03-03-2010 21:41:17
    
    3 rows selected.
    
    SQL> conn hoek/[email protected]
    Verbonden.
    SQL> select * from session_roles;
    
    ROLE
    ------------------------------
    DBA
    SELECT_CATALOG_ROLE
    HS_ADMIN_ROLE
    EXECUTE_CATALOG_ROLE
    DELETE_CATALOG_ROLE
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE
    GATHER_SYSTEM_STATISTICS
    SCHEDULER_ADMIN
    PLUSTRACE
    XDBADMIN
    XDBWEBSERVICES
    
    12 rows selected.
    Having DBA role doesn't matter.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    when I logoff either from the terminal and from the APEX's web igu admin
    which terminal?

    How is APEXs web gui disconnecting?

    I assume no regular disconnect in either case is made so no insert into the target table has taken place.

    But starting and ending a regular sql*plus session should verify that the trigger is indeed working as expected.
  • 741904
    741904 Member Posts: 31
    edited Mar 3, 2010 5:36PM
    Hello,

    With 'terminal' I meant pl*sql terminal.

    In order to be sure I'm well disconnected I have made a new trigger but now on the event on 'logon'. It works fine:

    CREATE OR REPLACE TRIGGER Trig7174
    AFTER LOGON ON CLASIE.SCHEMA
    CALL p7174

    where p7174 is a procedure inserting a value into a table:

    CREATE OR REPLACE PROCEDURE p7174 IS
    BEGIN
    INSERT INTO TEST1 VALUES('p7174');
    END;

    Then if I can logon I suppose it's only possible if I've been previously logoff.

    So to be clear, I connect through pl*sql and I disconnect from it by the 'disconnect' command.

    Thank for all suggestions.
    Regards.
    Claude

    Edited by: claude_belgique on Mar 3, 2010 2:33 PM
This discussion has been closed.