tests/ctags/db-trig.sql

   1 rem -----------------------------------------------------------------------
   2 rem URL:        http://www.orafaq.com/scripts/plsql/db-trig.txt
   3 rem Filename:   db-trig.sql
   4 rem Purpose:    Demonstrate database triggers (available from Oracle 8i)
   5 rem             Need DBA or CREATE ANY TRIGGER privs
   6 rem Date:       28-Aug-1998
   7 rem Author:     Frank Naude (frank@ibi.co.za)
   8 rem -----------------------------------------------------------------------
   9
  10 create or replace trigger restrict_login
  11         after logon       on database
  12 declare
  13         flag number := 0;
  14 begin
  15         select 1 into flag from sys.v_$session where program like '%sqlplus%';
  16         if flag = 1 then
  17                 raise_application_error(-20000, 'No access from sqlplus');
  18         end if;
  19 end;
  20 /
  21 show errors
  22
  23
  24 CREATE OR REPLACE TRIGGER startup_db
  25 after startup on database
  26 begin
  27     dbms_shared_pool.keep ('SYS.STANDARD','P');
  28     dbms_shared_pool.keep ('SYS.DBMS_STANDARD','P');
  29 end;
  30 /
  31 show errors