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

   <source lang="sql">

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.

 </source>

A system trigger(AFTER CREATE ON DATABASE) .

   <source lang="sql">

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>

 </source>

creating a logon/logoff auditing system using system-level triggers:

   <source lang="sql">

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>

 </source>

Log a server error to a table

   <source lang="sql">

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.

 </source>

Log a server error with information on time, user name, database name

   <source lang="sql">

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.

 </source>

Log database logon to a table

   <source lang="sql">

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>

 </source>

Logon trigger

   <source lang="sql">

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> --

 </source>

Save information about all errors in an after-servererror on database

   <source lang="sql">

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.

 </source>

Submit a job for altering user in an after-create-database trigger

   <source lang="sql">

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>

 </source>

Trigger for database startup event

   <source lang="sql">

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> --

 </source>

Trigger to log the database shutdown

   <source lang="sql">

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> --

 </source>

Oracle PL/SQL/Trigger/Database Trigger

Содержание

After logon database trigger

A system trigger(AFTER CREATE ON DATABASE) .

creating a logon/logoff auditing system using system-level triggers:

Log a server error to a table

Log a server error with information on time, user name, database name

Log database logon to a table

Logon trigger

Save information about all errors in an after-servererror on database

Submit a job for altering user in an after-create-database trigger

Trigger for database startup event

Trigger to log the database shutdown

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты