5/2/2001 -- In my last column, I showed you how to more easily reorganize tables and remove columns from tables using the ALTER TABLE … MOVE and ALTER TABLE … DROP COLUMN commands. This month I want to introduce you to Oracle 8i's event triggers and how they can be used to keep a record of user logon and logoff activity, and monitor data definition changes on database objects. You can also do other things with event triggers, such as configure instance settings on startup or clean things up on shutdown.

What's an Event Trigger?
Oracle has had support for triggers for a very long time. Triggers are useful to maintain database referential integrity, such as ensuring that an update to a primary key value does not orphan dependent foreign keys, or to enforce business rules that cannot be satisfied with a simple CHECK constraint on a column. In Oracle 8i, the concept of triggers has been expanded to cover database events such as user logon, instance startup, object creation or modification, changes of permissions, or an Oracle error. This means that you can have code execute when a user logs on, or the instance starts, and then record the action or change settings. This is quite useful if you need to keep track of the time spent by users accessing the database, or to track who creates, alters or drops objects.

Event triggers can be created on the database or the schema. When a trigger is defined on the database, it can track actions by all users; schema triggers can be used to track event activity in a single user's schema. For example, if you wanted to track all logon and logoff activity for users, you would create the trigger on the database. To track CREATE TABLE actions for Bob, you can create the trigger on Bob's schema.

Event triggers can also be invoked before the action takes place or after it has happened. Certain events, such as SHUTDOWN and LOGOFF, can only have the trigger fire before the event, while others can only fire after the event (e.g., LOGON, STARTUP and SERVERERROR). Most events support either timing, such as events on DCL and DDL commands.

Sometimes Users Make Mistakes…
I don't know what your experience as a DBA has been, but every once in a while I get a phone call from someone who tells me that they used to have a table (view, synonym, whatever) in their schema and now it's gone. Naturally, my first question is "What changed?" -- a nice way of saying "What did you do?" -- and the normal response is usually "Nothing."

Using Oracle's event trigger functionality, you can now create an event trigger to deal with DDL changes. For this purpose, you have several events you can trap for: CREATE, ALTER, DROP or the generic DDL.

For example, if you wanted to log every DDL action performed on the database into a table called DDLEvents, you can create a trigger on the database to log the activity, as in the following code example:

CREATE OR REPLACE TRIGGER DDLTrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO DDLEvents
 (UserName, DDLDate, DDLObjType, DDLObjOwner, DDLObjName, DDLEvent) VALUES
 (ora_login_user, sysdate, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent);
END;

Accounting for Time
One of things that some organizations find useful is to determine the length of time individuals spend being connected to the database. You can also gauge usage patterns for the database and track logon/logoff trends. While this may seem somewhat Big Brother-ish, for many organizations this is not an option and may be a legal requirement.

The AFTER LOGON and BEFORE LOGOFF events can have triggers created on them to log this activity for your users. You can capture when the user logs on and off, which instance (in a parallel server environment) the user logged on to, what IP address the client is connecting from and other information. A sample set of logon and logoff triggers to log activity to an AccountConnections table in the SYS schema could be something like this:

CREATE OR REPLACE TRIGGER LogonTrigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO AccountConnections
  (UserName, EventAction, EventDate, IP_Address, InstanceNum)
VALUES
  (ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num);
END;

CREATE OR REPLACE TRIGGER LogoffTrigger
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO AccountConnections
  (UserName, EventAction, EventDate, IP_Address, InstanceNum)
VALUES
  (ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num);
END;

You need to create separate triggers for each event, as Oracle does not currently support creating a single trigger for multiple database events. After creating these triggers, querying the AccountConnections table with SQL*Plus may result in output similar to the following:

SQL> col username format a10
SQL> col eventaction format a10
SQL> alter session set nls_date_format='YY-MM-DD HH:MI:SS AM';

Session altered.

SQL> SELECT UserName, EventAction, EventDate
  2  FROM AccountConnections
  3  ORDER BY EventDate DESC;

USERNAME   EVENTACTIO EVENTDATE
---------- ---------- --------------------
SYS        LOGON      01-04-21 12:05:10 PM
SCOTT      LOGOFF     01-04-21 12:05:07 PM
SYSTEM     LOGOFF     01-04-21 12:01:04 PM
SCOTT      LOGON      01-04-21 10:05:04 AM
SYSTEM     LOGON      01-04-21 10:01:32 AM
SYS        LOGOFF     01-04-21 12:04:54 AM

6 rows selected.

SQL>

Event Triggers Aren't Just for Tracking Activity…
Event triggers in Oracle 8i can be used for many other things besides logging activity on the database. They can be used to invoke jobs or other actions when a critical database error arises. You can also make use of them to properly configure a user's environment with NLS settings, activate roles, and so on. You can also use it to switch between resource consumer groups when an instance starts, or verify that the proper one is active, and so on. Although the above examples create event triggers on the database, if you want to limit a trigger's scope to a particular schema, you can create most event triggers (except AFTER LOGON, BEFORE LOGOFF, AFTER STARTUP or BEFORE SHUTDOWN) on the user's schema using the ON SCHEMA keywords. In this case, the event will only fire if it is invoked by the user whose schema created it, or on an object in that schema.

This Could Be a Series If I'm Not Careful
Oracle 8i has many features that can solve problems for an organization or a DBA. Event triggers present new possibilities for both. I alluded to others in the last few paragraphs of this column. If I'm not careful, I may be writing columns on them for a long time (or until Oracle 9i comes out)! If you have examples of things you have done with event triggers to make your life easier, please send them to me at so I can share them with the other readers in future columns.