| 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-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 |
Sometimes you may need to disable a trigger. For example, if there is an error in a
system trigger (for example an AFTER STARTUP trigger) it may be impossible to start the database. The solution is to disable the
system trigger while the issue is resolved.
Or you may have a DDL trigger defined to trap all the structural changes in the production database. This trigger prevents the
creation of temporary tablespace, so you would need to disable that trigger before proceeding. 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" = FALSE 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; |
