In the series of Database Event Triggers so far we have learned how to create and work with database event Logon and Logoff triggers. Now the only options which are left are Database event Startup and Shutdown Triggers. Today in this blog we will learn how to create these triggers with some easy to understand examples.

Startup Trigger.

Startup triggers execute during the startup process of the database. In order to create a database event trigger for shutdown and startup events we either need to logon to the database as a user with DBA privileges such as sys or we must possess the ADMINISTER DATABASE TRIGGER system privilege.

Suggested Reading: System Privileges

Example

Step1: Logon to the database

In order to create a trigger on Startup Database Event first we will have to logon to our database using the user SYS with DBA privileges.

Step 2: Create a Table

To store the data generated by the execution of trigger we will require a table.

CREATE TABLE startup_audit 
(
  Event_type  VARCHAR2(15),
  event_date  DATE,
  event_time  VARCHAR2(15)
);

Step 3: Create the database Event Startup Trigger

In this step we will create a trigger which will execute every time the database in which it is created starts up.

CREATE OR REPLACE TRIGGER startup_audit
AFTER STARTUP ON DATABASE
BEGIN
  INSERT INTO startup_audit VALUES
(
    ora_sysevent,
    SYSDATE,
    TO_CHAR(sysdate, 'hh24:mm:ss')
  );
END;
/

On successful execution this trigger will insert a row of data each time database starts up.

Shutdown Triggers

SHUTDOWN triggers execute before database shutdown processing is performed. Similar to the startup trigger, only a user with DBA role or ADMINISTER DATABASE TRIGGER system privilege can create a shutdown trigger.

Example.

First 2 steps of creating a database event shutdown triggers are same as that of the startup trigger which we saw above.

CREATE OR REPLACE TRIGGER tr_shutdown_audit
BEFORE SHUTDOWN ON DATABASE
BEGIN
  INSERT INTO startup_audit VALUES(
    ora_sysevent,
    SYSDATE,
    TO_CHAR(sysdate, 'hh24:mm:ss')
  );
END;
/

Table used in this trigger is the same one which we created during the coding of the Startup trigger above.

SHUTDOWN triggers execute only when the database is shut down using NORMAL or IMMEDIATE mode. They do not execute when the database is shut down using ABORT mode or when the database crashes.

You can also use shutdown database event triggers for gathering your database system statistics. Here is an example

CREATE OR REPLACE TRIGGER before_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
 gather_system_stats;
END;
/

Hope you enjoyed reading. If so then make sure to share this blog with the #RebellionRider. That’s all in this blog stay tuned for more such informative blogs. Thanks & have a great day!