Oracle 系统触发器
来源:互联网 发布:二手手机淘宝店 编辑:程序博客网 时间:2024/05/21 17:43
--防止误删除表
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_USERVARCHAR2(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;
/
- Oracle 系统触发器
- Oracle系统触发器
- Oracle触发器(建立系统事件触发器)1
- Oracle触发器(建立系统事件触发器)2
- Oracle触发器(建立系统事件触发器)
- oracle的系统事件触发器
- Oracle基础知识(八) - 触发器的分类:系统触发器
- oracle 系统的ddl触发器研究
- 触发器六(系统触发器)
- oracle 触发器
- Oracle触发器
- Oracle触发器
- oracle触发器
- ORACLE触发器
- oracle触发器
- Oracle 触发器
- oracle触发器
- Oracle触发器
- 打印图形
- 《大学》
- MySQL的表锁问题(一)
- 面试题:百度二面(题目看来的)
- ruby
- Oracle 系统触发器
- RK30系列替换开机第一屏教程,n70双擎成功,非完美,希望高手能进一步完善改进~~~
- Android监听WebView滑动到底部
- apache2.2的目录文件及 虚拟主机的配置
- Windows 8.1 Preview的新功能和新API
- VC++环境下连续自增(自减)运算规则 i++ ++i
- freemarker入门笔记之一:永恒的helloword
- zendstudio 9 快捷键
- MyEclipes安装报错