Oracle PL/SQL/Trigger/Database Trigger
Содержание
- 1 After logon database trigger
- 2 A system trigger(AFTER CREATE ON DATABASE) .
- 3 creating a logon/logoff auditing system using system-level triggers:
- 4 Log a server error to a table
- 5 Log a server error with information on time, user name, database name
- 6 Log database logon to a table
- 7 Logon trigger
- 8 Save information about all errors in an after-servererror on database
- 9 Submit a job for altering user in an after-create-database trigger
- 10 Trigger for database startup event
- 11 Trigger to log the database shutdown
After logon database trigger
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER change_schema AFTER logon ON DATABASE
2 begin
3 execute immediate("alter session set current_schema=app_master");
4 end;
5 /
Trigger created.
SQL>
SQL> drop trigger change_schema;
Trigger dropped.
A system trigger(AFTER CREATE ON DATABASE) .
SQL>
SQL>
SQL> CREATE TABLE ddl_creations (
2 user_id VARCHAR2(30),
3 object_type VARCHAR2(20),
4 object_name VARCHAR2(30),
5 object_owner VARCHAR2(30),
6 creation_date DATE);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LogCreations
2 AFTER CREATE ON DATABASE
3 BEGIN
4 INSERT INTO ddl_creations (user_id, object_type, object_name,
5 object_owner, creation_date)
6 VALUES (USER, SYS.DICTIONARY_OBJ_TYPE, SYS.DICTIONARY_OBJ_NAME,
7 SYS.DICTIONARY_OBJ_OWNER, SYSDATE);
8 END LogCreations;
9 /
Trigger created.
SQL>
SQL> DROP TABLE ddl_creations;
Table dropped.
SQL>
SQL> drop trigger LOGCREATIONS;
Trigger dropped.
SQL>
creating a logon/logoff auditing system using system-level triggers:
SQL>
SQL> CREATE TABLE logon_audit(
2 user_id VARCHAR2(30),
3 sess_id NUMBER(10),
4 logon_time DATE,
5 logoff_time DATE,
6 host VARCHAR2(20));
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER logon_audit_trig AFTER LOGON
2 ON DATABASE
3 BEGIN
4 INSERT INTO logon_audit VALUES (user, sys_context("userenv", "sessionid"),sysdate,null,sys_context("userenv", "host"));
5 END;
6 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER logoff_audit_trig BEFORE LOGOFF
2 ON DATABASE
3 BEGIN
4 INSERT INTO logon_audit VALUES (user,sys_context("userenv", "sessionid"),null,sysdate,sys_context("userenv", "host"));
5 END;
6 /
Trigger created.
SQL> drop table logon_audit;
Table dropped.
SQL>
SQL>
Log a server error to a table
SQL>
SQL> CREATE TABLE error_log (
2 timestamp DATE,
3 username VARCHAR2(30),
4 instance NUMBER,
5 database_name VARCHAR2(50),
6 error_stack VARCHAR2(2000)
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
2 AFTER SERVERERROR ON DATABASE
3 BEGIN
4 INSERT INTO error_log
5 VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
6 DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
7 END LogErrors;
8 /
Trigger created.
SQL>
SQL>
SQL> BEGIN
2 -- This is a syntax error!
3 DELETE FROM students
4 END;
5 /
END;
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
SQL>
SQL>
SQL> SELECT * FROM error_log;
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 sqle 1
XE
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expectin
g one of the following:
begin case declare end exception exit for goto if loop mod
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock inser
t
open rollback savepoint set sql execute commit forall merg
e
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
pipe
SQL>
SQL> DROP TABLE error_log;
Table dropped.
SQL>
SQL> drop trigger LOGERRORS;
Trigger dropped.
Log a server error with information on time, user name, database name
SQL>
SQL> CREATE TABLE error_log (
2 timestamp DATE,
3 username VARCHAR2(30),
4 instance NUMBER,
5 database_name VARCHAR2(50),
6 error_stack VARCHAR2(2000)
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
2 AFTER SERVERERROR ON DATABASE
3 BEGIN
4 INSERT INTO error_log
5 VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
6 DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
7 END LogErrors;
8 /
Trigger created.
SQL>
SQL>
SQL> DECLARE
2 v_StringVar VARCHAR2(2);
3 BEGIN
4 -- This is a runtime error!
5 v_StringVar := "abcdef";
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5
SQL>
SQL>
SQL>
SQL> SELECT * FROM error_log;
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 sqle 1
XE
ORA-06502: PL/SQL: numeric or value error: character string b
uffer too small
ORA-06512: at line 5
SQL>
SQL> DROP TABLE error_log;
Table dropped.
SQL>
SQL> drop trigger LOGERRORS;
Trigger dropped.
Log database logon to a table
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER LogAllConnects
2 AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT INTO MyTable
5 VALUES (3, "LogAllConnects fired!");
6 END LogAllConnects;
7 /
Trigger created.
SQL>
SQL> SELECT * FROM MyTable;
no rows selected
SQL> drop table MyTable;
Table dropped.
SQL>
Logon trigger
SQL>
SQL> set echo on
SQL>
SQL>
SQL> create or replace trigger logon_trigger
2 after logon on database
3 begin
4 if ( user = "YourName" ) then
5 execute immediate
6 "ALTER SESSION SET EVENTS ""10046 TRACE NAME CONTEXT FOREVER, LEVEL 4""";
7 end if;
8 end;
9 /
Trigger created.
SQL>
SQL>
SQL> --
Save information about all errors in an after-servererror on database
SQL> create table userLog
2 (error_date date,
3 username varchar2(30),
4 error_msg varchar2(2000),
5 error_sql varchar2(2000));
Table created.
SQL>
SQL> create or replace trigger log_errors after servererror on database
2 declare
3 sql_text ora_name_list_t;
4 msg varchar2(2000) := null;
5 stmt varchar2(2000):= null;
6 begin
7 for i in 1 .. ora_server_error_depth loop
8 msg := msg||ora_server_error_msg(i);
9 end loop;
10 for i in 1..ora_sql_txt(sql_text) loop
11 stmt := stmt||sql_text(i);
12 end loop;
13 insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
14 end;
15 /
Trigger created.
SQL>
SQL>
SQL>
SQL> drop table userLog;
Table dropped.
Submit a job for altering user in an after-create-database trigger
SQL>
SQL> create or replace procedure AlterUser (usernameIn in varchar2) is
2 begin
3 execute immediate ( "alter user " ||usernameIn|| " default tablespace users");
4 end;
5 /
Procedure created.
SQL>
SQL> create or replace trigger SystemAlterUser
2 after create on database
3 declare
4 jobno number;
5 begin
6 if ora_dict_obj_type = "USER" then
7 dbms_job.submit(job => jobno, what => "alteruser("""||ora_dict_obj_name||""");");
8 end if;
9 end;
10 /
Trigger created.
SQL>
SQL>
SQL>
Trigger for database startup event
SQL>
SQL> CREATE TABLE uptime_log (
2 database_name VARCHAR2(30),
3 event_name VARCHAR2(20),
4 event_time DATE,
5 triggered_by_user VARCHAR2(30)
6 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER log_startup
2 AFTER STARTUP ON DATABASE
3 BEGIN
4 INSERT INTO uptime_log
5 (database_name,
6 event_name,
7 event_time,
8 triggered_by_user)
9 VALUES (sys.database_name,
10 sys.sysevent,
11 sysdate,
12 sys.login_user);
13 COMMIT;
14 END;
15 /
Trigger created.
SQL>
SQL> drop table uptime_log;
Table dropped.
SQL>
SQL>
SQL> --
Trigger to log the database shutdown
SQL>
SQL> CREATE TABLE uptime_log (
2 database_name VARCHAR2(30),
3 event_name VARCHAR2(20),
4 event_time DATE,
5 triggered_by_user VARCHAR2(30)
6 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER log_shutdown
2 BEFORE SHUTDOWN ON DATABASE
3 BEGIN
4 INSERT INTO uptime_log
5 (database_name,
6 event_name,
7 event_time,
8 triggered_by_user)
9 VALUES (sys.database_name,
10 sys.sysevent,
11 sysdate,
12 sys.login_user);
13 COMMIT;
14 END;
15 /
Trigger created.
SQL>
SQL> drop table uptime_log;
Table dropped.
SQL>
SQL> --