============================================================
DATABASE EVENT TRIGGERS AND DDL EVENT TRIGGERS IN ORACLE8I
============================================================

PURPOSE
---------

8.1.X에서 새롭게 소개된 Database내에서 database event에 의해서 fire되는
trigger에 대해 설명하고자 한다.

Explanation
-------------

8.1.X 부터는 Database내에서 database event에 의해서 fire되는 trigger로
새로운 type의 trigger을 제공한다. Database event로는 SERVERERROR, user logon,
user logoff,database startup ,database shutdown이다. 또한 DDL event에 따라
fire되는 trigger도 만들 수 있다.

-----------------------
Type Timing
-----------------------
LOGON AFTER
LOGOFF BEFORE
STARTUP AFTER
SHUTDOWN BEFORE
SERVERERROR AFTER
-----------------------

Example
---------

예제 1 ) user logon을 audit하는 trigger

CREATE TABLE log_info (login_date DATE, username VARCHAR2(20));

CREATE OR REPLACE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
insert into system.log_info values(sysdate,'ORA-1017');
END IF;
END;
/

CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE
BEGIN
insert into system.log_info values(sysdate,user);
END;
/

$ sqlplus scott/tiger ( A session )

$ sqlplus system/manager ( B session )

SQL> select * from log_info;

LOGIN_DATE USERNAME
---------- --------------------
2000/06/15 SCOTT
2000/06/15 SYSTEM

만약 ORA-01017 error가 발생하면 log_errors trigger가 error가 발생한 시간과
error number을 가지고 fire된다. user가 logon을 성공하면 logon_audit trigger가
시간과 username을 기록할 것이다.

$ sqlplus system/manager

ERROR:
ORA-04098: trigger 'LOGON_AUDIT' is invalid and failed re-validation

AFTER LOGON trigger을 사용함에 있어서 logging을 남기는 table(log_info)에 space가
부족해서 위와 같이 ORA-04098 error와 함께 DBA user을 포함한 모든 user가 logon 하지
못하는 경우가 발생할 수도 있다.

이럴 경우 아래와 같이 해결한다.

SVRMGR> connect internal
Connected.
SVRMGR> ALTER TRIGGER system.logon_audit DISABLE;
Statement processed.

예제 2 ) Database uptime을 logging하는 trigger

CREATE TABLE up_time (action_date DATE, action VARCHAR2(20));

CREATE OR REPLACE TRIGGER db_start AFTER STARTUP ON DATABASE
BEGIN
insert into system.up_time values(sysdate,'STARTUP');
END;
/

CREATE OR REPLACE TRIGGER db_stop BEFORE SHUTDOWN ON DATABASE
BEGIN
insert into system.up_time values(sysdate,'SHUTDOWN');
END;
/

다음과 같이 실행해 본다.

SHUTDOWN IMMEDIATE
STARTUP
SHUTDOWN ABORT
STARTUP

SQL> select to_char(action_date,'DD-MON-YY:HH:MI:SS), action from up_time;

ACTION_DATE ACTION
------------------ --------------------
15-JUN-00:03:31:35 SHUTDOWN
15-JUN-00:03:32:09 STARTUP
15-JUN-00:03:32:47 STARTUP


위와 같이 query해 보면 SHUTDOWN ABORT가 빠져 있는 것을 볼 수 있다.
SHUTDOWN ABORT시에는 db_stop trigger가 fire되지 않는다.


예제 3 ) Database startup시에 package을 shared_pool pin하는 trigger


SVRMGR> @?/rdbms/admin/dbmspool

CREATE OR REPLACE TRIGGER db_start_pin AFTER STARTUP ON DATABASE
BEGIN
sys.dbms_shared_pool.keep('SYS.STANDARD');
END;
/


예제 4 ) User가 logon시에 NLS_DATE_FORMAT을 set하는 LOGON trigger

CREATE OR REPLACE TRIGGER on_logon AFTER LOGON ON scott.schema
DECLARE
v_cur integer;
v_cur2 integer;
BEGIN
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur,'alter session set nls_date_format=''DD-MON-YYYY''',
dbms_sql.native);
v_cur2 := dbms_sql.execute(v_cur);
END;
/

$sqlplus scott/tiger

SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER
------------------------------------------------------------
VALUE
------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-YYYY