в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:37Z

1 версия

Новая страница

== Database level trigger: AFTER STARTUP ON DATABASE==



<source lang="sql">
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></source>

== Database level trigger: BEFORE SHUTDOWN ON DATABASE==



<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>
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.</source>

== Database logoff trigger==



<source lang="sql">
SQL>
SQL> CREATE TABLE session_logon_statistics
2 (user_logged VARCHAR2(30),
3 start_time DATE,
4 end_time DATE);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER logoff_log_trigger
2 BEFORE LOGOFF
3 ON DATABASE
4 BEGIN
5 UPDATE session_logon_statistics
6 SET end_time = SYSDATE
7 WHERE user_logged = USER
8 AND end_time IS NULL;
9 END;
10 /
Trigger created.
SQL>
SQL> SELECT user_logged,
2 TO_CHAR(start_time, "MM/DD/YYYY HH24:MI:SS") "START TIME",
3 TO_CHAR(end_time, "MM/DD/YYYY HH24:MI:SS") "END TIME"
4 FROM session_logon_statistics
5 where rownum < 50
6 order by user_logged, start_time;
no rows selected
SQL>
SQL> drop trigger logoff_log_trigger;
Trigger dropped.
SQL>
SQL> drop table session_logon_statistics;
Table dropped.</source>

== Database logon trigger==



<source lang="sql">
SQL>
SQL> CREATE TABLE session_logon_statistics
2 (user_logged VARCHAR2(30),
3 start_time DATE,
4 end_time DATE);
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER logon_log_trigger
2 AFTER LOGON
3 ON DATABASE
4 BEGIN
5 INSERT INTO session_logon_statistics
6 (user_logged, start_time)
7 VALUES
8 (USER, SYSDATE);
9 END;
10 /
Trigger created.
SQL>
SQL>
SQL>
SQL> SELECT user_logged,
2 TO_CHAR(start_time, "MM/DD/YYYY HH24:MI:SS") "START TIME",
3 TO_CHAR(end_time, "MM/DD/YYYY HH24:MI:SS") "END TIME"
4 FROM session_logon_statistics
5 where rownum < 50
6 order by user_logged, start_time;
no rows selected
SQL>
SQL>
SQL> drop trigger logon_log_trigger;
Trigger dropped.
SQL>
SQL> drop table session_logon_statistics;
Table dropped.</source>

== New Database triggers==



<source lang="sql">
SQL> CREATE OR REPLACE TRIGGER On_Logon
2 AFTER LOGON
3 ON SCHEMA
4 BEGIN
5 DBMS_UTILITY.ANALYZE_SCHEMA(sys.login_user,"ESTIMATE");
6 END;
7 /
Trigger created.
SQL>
SQL> drop trigger On_Logon;
Trigger dropped.</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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Trigger/Database Level Trigger - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия