Database event triggers also known as system event triggers come into action when some system event occurs such as database log on, log off, start up or shut down. These types of triggers are majorly used for monitoring activity of the system events and have been proved quite a powerful tool for a DBA.
Types of Database Event Triggers.
- Schema Level Event Triggers
- Database Level Event Triggers
Schema level event triggers can work on some specific schemas while the database event triggers have database wide scope. In other words database event triggers can be created to monitor the system event activities of either a specific user/schema or the whole database.
Schema level event triggers can be created by any user of your database who has CREATE TRIGGER system privilege while the database event trigger can only be created by privileged user such as SYS or SYSTEM who has ‘Administrative Database Trigger’ System Privileges.
Suggested Reading: Introduction To Privileges In Oracle Database [Interview Edition]
CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER database_event ON database/schema BEGIN PL/SQL Code END; /
Please watch the video tutorial for detailed explanation of the syntax.
Example. Schema Level Event Trigger.
Suppose user HR is a control freak and wants to monitor its every log on and log off activity. In this case what HR can do is, create event triggers on Log on and log off database event in its own schema.
Step 1: Connect to the database
Connect to the database using the user/schema in which you want to create the trigger. For the demonstration I will connect using my HR user.
C:/> Conn hr/hr
Or if you are using SQL Developer then read here on how to connect to the database using the same.
Step 2: Create a Table
Next you will need a table to store the logon and logoff data.
CREATE TABLE hr_evnt_audit ( event_type VARCHAR2(30), logon_date DATE, logon_time VARCHAR2(15), logof_date DATE, logof_time VARCHAR2(15) );
Step3: Write the trigger Logon Schema Event Trigger.
Now you are connected to the database using the desired user and also have the table ready to store the data. The only thing which is left is the trigger.
This trigger will fire every time HR user logs on to the database and respective values will be stored into the table which we just created in the step 2.
CREATE OR REPLACE TRIGGER hr_lgon_audit AFTER LOGON ON SCHEMA BEGIN INSERT INTO hr_evnt_audit VALUES( ora_sysevent, sysdate, TO_CHAR(sysdate, 'hh24:mi:ss'), NULL, NULL ); COMMIT; END; /
That is how we create a LogOn event trigger in Oracle Database. You can watch the video tutorial on my YouTube channel on the same topic where I created and tested the trigger live. And Stay tuned as in the next tutorial we will see how to create a logoff event trigger in Oracle Database.
Please Share this blog on your Facebook, Twitter and by any means you find convenient with the hashtag #RebellionRider or you can also tag me for the immediate shoutout on my wall.
That’s it guys. Hope you enjoyed reading. Thanks & have a great day!