| General |
| Data Dictionary Objects |
| dba_triggers |
all_triggers |
user_triggers |
trigger$
|
|
| Related System Privileges |
administer database trigger
alter any trigger
create trigger
create any trigger
drop any trigger |
System Event Trigger Types |
AFTER STARTUP
BEFORE SHUTDOWN
AFTER LOGON
BEFORE LOGOFF
AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover
AFTER SUSPEND
AFTER SERVERERROR (does not trap ...
- ORA-01403: no data found (this is in the
Oracle docs but does not seem to be correct)
- ORA-01422: exact fetch returns more than requested number of rows
- ORA-01423: error encountered while checking for extra rows in exact
fetch
- ORA-01034: ORACLE not available
- ORA-04030: out of process memory when trying to allocate string bytes
(string, string)
|
| Database Level Event Triggers |
SELECT a.obj#,
a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 0;
|
| Schema Level Event Triggers |
SELECT a.obj#,
a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 88;
|
Disabling System Triggers |
If there is an error in a system trigger, for example an AFTER STARTUP
trigger, it may be impossible to start the database. The following is the method for disabling system triggers. |
/ as sysdba
set linesize 150
col NAME format a30
col VALUE format a20
col DESCRIPTION format a60
SELECT x.ksppinm NAME,
y.ksppstvl VALUE,
ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';
ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH; |
| |
| Create SYSTEM
EVENT TRIGGER |
System Trigger Demo
Demo table and the logproc procedure (below) must be built before the trigger will
compile |
CREATE OR REPLACE TRIGGER <trigger_name>
<trigger_type> ON DATABASE
CALL <procedure_name>
/ |
CREATE TABLE
connection_audit (
login_date DATE,
user_name VARCHAR2(30));
CREATE OR REPLACE PROCEDURE logproc IS
BEGIN
INSERT INTO connection_audit
(login_date, user_name)
VALUES
(SYSDATE, USER);
END logproc;
/
CREATE OR REPLACE TRIGGER logintrig
AFTER LOGON ON DATABASE
CALL logproc
/
conn sh/sh
conn scott/tiger
conn uwclass/uwclass
SELECT *
FROM connection_audit;
drop trigger logintrig; |
Demo Application To Log Logon Attempts ... Both
Successful And Failed |
CREATE OR REPLACE TRIGGER <trigger_name>
<trigger_type> ON DATABASE
<trigger_code>
END <trigger_name>;
/ |
truncate table connection_audit;
-- trigger to trap successful logons
CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO connection_audit
(login_date, user_name)
VALUES
(SYSDATE, USER);
END logon_audit;
/
conn scott/tiger
conn sh/sh
conn / as sysdba
conn uwclass/uwclass
SELECT *
FROM connection_audit;
-- trigger to trap unsuccessful logons
CREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO connection_audit
(login_date, user_name)
VALUES
(SYSDATE, 'ORA-1017');
END IF;
END logon_failures;
/
conn scott/tigre
conn abc/def
conn test/test
conn uwclass/uwclass
SELECT *
FROM connection_audit;
/*
other errors that could be trapped include:
ORA-01004 - default username feature not supported
ORA-01005 - null password given
ORA-01035 - Oracle only available to users with restricted session priv
ORA-01045 - create session privilege not granted
*/ |
Demo To Log System Errors |
CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user VARCHAR2(30),
db_name VARCHAR2(9),
error_stack VARCHAR2(2000),
captured_sql VARCHAR2(1000));
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
captured_sql VARCHAR2(1000);
BEGIN
SELECT q.sql_text
INTO captured_sql
FROM gv$sql q, gv$sql_cursor c, gv$session s
WHERE s.audsid = audsid
AND s.prev_sql_addr = q.address
AND q.address = c.parent_handle;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
/ |
After Logon Trigger for Tracing |
CREATE OR REPLACE
TRIGGER trace_trig
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
IF (USER = 'UWCLASS') THEN
execute immediate sqlstr;
END IF;
END trace_trig;
/ |
After Logon Trigger for Outlines |
CREATE OR REPLACE
TRIGGER trace_trig
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'alter session set use_stored_outlines =
uw_outlines';
BEGIN
IF (USER = 'UWCLASS') THEN
execute immediate sqlstr;
END IF;
END trace_trig;
/ |
| |
| Drop SYSTEM EVENT
TRIGGER |
| Drop Trigger |
DROP TRIGGER <trigger_name>; |
|
DROP TRIGGER logon_failures; |