Logon Counter Trigger - Exercise

Contact Us or call 1-877-932-8228
Logon Counter Trigger - Exercise

Logon Counter Trigger

Duration: 15 to 20 minutes.
  1. Create a table called LOGON_COUNT with a single column of type NUMBER named TOTAL_LOGINS with a single row with a value of zero.
  2. Create an AFTER LOGON ON SCHEMA trigger that increments this field each time a user logs in.
  3. Query the LOGON_COUNT table.
  4. Log off and log on. Verify that the count incremented.
  5. Check the status in user_objects and user_triggers. Explain the results.
  6. Drop the LOGON_COUNT table. Check the status in user_objects and user_triggers.
  7. Drop the trigger, and purge the recycle bin.


create table logon_count
select 0 as total_logins
from dual;

select *
from logon_count;

create or replace trigger logon_alert
after logon on schema
  update logon_count 
  set total_logins = total_logins +1;

select * 
from logon_count;

--Log off an log on 
select *
from logon_count;

select status 
from user_triggers 
where trigger_name='LOGON_ALERT';

select status 
from user_objects 
where object_name='LOGON_ALERT';

--The user_triggers status indicates whether the
--    trigger is enabled or disabled.
--The user_objects status indicates whether the
--    trigger successfully compiled.

drop table logon_count;

purge recyclebin;

Code Explanation