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 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
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 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.
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!