28 November 2006

System Triggers In Oracle

One of the features of Oracle database is to create global triggers called system triggers. They are executed in spesific situations. For more information check docs.
Below a simple demonstration that shows how to create this type of triggers. A LOGON trigger created to audit logins of users:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL>
SQL> drop table user_logs;

Table dropped

SQL> create table user_logs( osuser varchar2(32), datetime date );

Table created

SQL> CREATE OR REPLACE TRIGGER log_on_trg
2 AFTER logon ON hr.SCHEMA
3 BEGIN
4 INSERT INTO user_logs
5 VALUES
6 ((SELECT distinct ss.OSUSER FROM v$session ss WHERE ss.SID = userenv('sid')), SYSDATE);
7 END;
8 /

Trigger created

SQL> show err;
No errors for TRIGGER SYS.LOG_ON_TRG

SQL> conn hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> conn sys/0000@XE as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> SELECT * FROM user_logs;

OSUSER                            DATETIME
-------------------------------- -----------
DELL-\Administrator               28.11.2006
DELL-\Administrator               28.11.2006

SQL>

No comments: