Oracle System Triggers – the list and sample script
Posted by
Jiltin 23 February, 2009 1,072 views
- Using profile option ‘Initialization SQL Statement -Custom’
- Handy tkprof sql creating script using bash shell unix, linux with oracle environment
- Oracle Applications Reports (Reports 6i) – Technical – Interview – Question Answers
- How To Add Responsibility to USER using pl/sql? – Oracle Applications
- Oracle Application Technical Questions/snippets, Basic Terminology!
Oracle System triggers can be delineated into two categories: those based on Data Definition Language (DDL) statements, and those based upon database events. Use of system triggers can greatly expand a DBA’s ability to monitor database activity and events.
| Event | Applicable |
| STARTUP | AFTER |
| SHUTDOWN | BEFORE |
| SERVERERROR | AFTER |
| LOGON | AFTER |
| LOGOFF | BEFORE |
| CREATE | BEFORE and AFTER |
| DROP | BEFORE and AFTER |
| ALTER | BEFORE and AFTER |
Sample Trigger statement
CREATE TRIGGER TRG_LOGON after logon ON DATABASE
begin
IF (user=‘SUB’) then
execute immediate ‘alter session set tracefile_identifier = TEST_TEST’;
EXECUTE IMMEDIATE ‘alter session set MAX_DUMP_FILE_SIZE = unlimited’;
execute immediate ‘alter session set TIMED_STATISTICS = TRUE’;
execute immediate ‘alter session set events ‘‘10046 trace name context forever, level 8′”;
end IF;
end ;
begin
IF (user=‘SUB’) then
execute immediate ‘alter session set tracefile_identifier = TEST_TEST’;
EXECUTE IMMEDIATE ‘alter session set MAX_DUMP_FILE_SIZE = unlimited’;
execute immediate ‘alter session set TIMED_STATISTICS = TRUE’;
execute immediate ‘alter session set events ‘‘10046 trace name context forever, level 8′”;
end IF;
end ;
Categories :
Scripts Oracle
Comments
No comments yet.