Oracle 系统触发器

--防止误删除表

CreateOrReplaceTrigger sys_trg_drop_ddl

  BeforedropOnDatabase

 

/*

 NAME       : sys_trg_drop_ddl.sql

 PURPOSE    : FOR sys trigger dropobject

 AUTHOR     : 

 OUTPUT     :

 

 REVISIONS

 VER    DATE          AUTHOR            DESCRIPTION

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

 0.1    2013-06-01                    - Init Version

 

 */

 

Declare

 dll_exception Exception;

  PragmaException_Init(dll_exception, -1031);

Begin

 

  --for testing then comma the return

  --Return;

 

  --the table needed to drop must startwith the tmp_ (underscore)

  If (ora_dict_obj_type ='TABLE')And ora_dict_obj_nameNotLike'TMP\_%'

   Escape'\'Then

   Raise dll_exception;

  EndIf;

Exception

  When dll_exceptionThen

   raise_application_error(-20000,

                            'Can not drop the table ' || ora_dict_obj_name ||

                            ' ,Only the table name start with thetmp_ ,then can droped!');

End;

/

 

 

--监控用户登入登出日志

-- Create table

createtable T_LOG_AUDIT

(

 D_DATE         DATE,

 S_SESSION_USER VARCHAR2(50),

 S_IP_ADDRESS   VARCHAR2(50),

 S_TERMINAL     VARCHAR2(50),

 S_DB_NAME      VARCHAR2(50),

 S_HOST         VARCHAR2(50),

 S_OS_USER      VARCHAR2(50),

 S_SESSIONID    VARCHAR2(50),

 S_INSTANCE     VARCHAR2(50),

 N_STATUS       NUMBER

);

-- Add comments to the table

commentontableT_LOG_AUDIT

  is'进出日志监控';

-- Add comments to the columns

commentoncolumnT_LOG_AUDIT.D_DATE

  is'日期';

commentoncolumnT_LOG_AUDIT.S_SESSION_USER

  is'登陆的用户';

commentoncolumnT_LOG_AUDIT.S_IP_ADDRESS

  is'IP地址';

commentoncolumnT_LOG_AUDIT.S_TERMINAL

  is'终端';

commentoncolumnT_LOG_AUDIT.S_DB_NAME

  is'DB名字';

commentoncolumnT_LOG_AUDIT.S_HOST

  is'HOST';

commentoncolumnT_LOG_AUDIT.S_OS_USER

  is'操作系统用户';   

commentoncolumnT_LOG_AUDIT.S_SESSIONID

  is'SESSIONID';

commentoncolumnT_LOG_AUDIT.S_INSTANCE

  is'实例ID';

commentoncolumnT_LOG_AUDIT.N_STATUS

  is'1登入 2登出';

 

CreateOrReplaceTrigger sys_trg_logon_audit

  After logonOnDatabase

/*

 NAME       :sys_trg_logon_audit.sql

 PURPOSE    : FOR sys trigger logonaudit

 AUTHOR     : 

 OUTPUT     :

 

 REVISIONS

 VER    DATE          AUTHOR            DESCRIPTION

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

 0.1    2013-06-01                 - Init Version

 

 */

Begin

  InsertInto system.t_log_audit

   SelectSysdate,

          sys_context('USERENV','SESSION_USER') session_user,

          sys_context('USERENV','IP_ADDRESS') ip_address,

          sys_context('USERENV','TERMINAL') terminal,

          sys_context('USERENV','DB_NAME') db_name,

          sys_context('USERENV','HOST')host,

          sys_context('USERENV','OS_USER') os_user,

          sys_context('USERENV','SESSIONID') sessionid,

          sys_context('USERENV','INSTANCE')instance,

          1

     From dual;

End;

/

 

CreateOrReplaceTrigger sys_trg_logoff_audit

  Before logoffOnDatabase

/*

 NAME       :sys_trg_logoff_audit.sql

 PURPOSE    : FOR sys triggerlogoff audit

 AUTHOR     : 

 OUTPUT     :

 

 REVISIONS

 VER    DATE          AUTHOR            DESCRIPTION

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

 0.1    2013-06-01                - Init Version

 

 */

Begin

  InsertInto system.t_log_audit

   SelectSysdate,

          sys_context('USERENV','SESSION_USER') session_user,

          sys_context('USERENV','IP_ADDRESS') ip_address,

          sys_context('USERENV','TERMINAL') terminal,

          sys_context('USERENV','DB_NAME') db_name,

          sys_context('USERENV','HOST') host,

          sys_context('USERENV','OS_USER') os_user,

          sys_context('USERENV','SESSIONID') sessionid,

          sys_context('USERENV','INSTANCE')instance,

          2

     From dual;

End;

/