System-Level Event Triggers

You can create triggers such that they will fire when certain system-level events are raised. System-level events, also called as database events, are associated with entire database instance or schema and not with individual table or row. The system-level events can be defined at the database level or schema level.

The system-level events include the following:

1. Database STARTUP and SHUTDOWN
2. Server error message events (SERVERERROR)

The startup and shutdown events are associated with the database instance. The server error message events, on the other hand can be associated with either the database instance or with a particular schema.

The attributes of these triggers include system event, instance number, database name, and error number.

STARTUP Triggers
The STARTUP triggers fire immediately after the database is opened by an instance.
The following example will show how to create the STARTUP trigger:

CREATE OR REPLACE TRIGGER Trig_Startup
AFTER STARTUP
Cursors and Triggers 8-59
ON DATABASE
BEGIN
DBMS_OUTPUT.ENABLE;
END;
/

SHUTDOWN trigger
The SHUTDOWN trigger is fired when an instance of the server starts shutting down.
You can use these triggers to shut down the instance applications completely, when the database shuts down.

The following example will show how to create the SHUTDOWN Trigger:

Enter the following statement to create the table error_info:

CREATE TABLE SHUTDOWN_Info(
User_name VARCHAR2(30),
Logon_date DATE);

After creating the table, the next step is to create a system-level SHUTDOWN trigger that will fill information in the table SHUTDOWN_Info at the time you log on.

The following code will create the SHUTDOWN trigger:

CREATE OR REPLACE TRIGGER Trig_Startup
AFTER SHUTDOWN
ON DATABASE
DECLARE
user_name VARCHAR2(20);
sys_date DATE;
BEGIN
SELECT user, SYSDATE INTO user_name, sys_date
FROM Dual;
INSERT INTO SHUTDOWN_Info VALUES
(user_name, sys_date);
COMMIT;
END;
/