Database & DDL Triggers in Oracle

In this post, we shall create a series of triggers to illustrate the use of Database triggers in Oracle.

Database Triggers:-

Database triggers are fired on Login & Logout. Let us create a trigger to maintain a list of logins & logouts. First a table to store the login information:-


create table LoginStats(username varchar(50),datetimeoflogin date)

Here is the trigger:-

create or replace trigger logintrigger
after logon on database
begin
insert into LoginStats values(ora_login_user,sysdate);
end;

A logon trigger can only be a after trigger.



A trigger for Logout

The table :

create table LogoutStats(username varchar(50),datetimeoflogout date)


create or replace trigger logofftrigger
before logoff on database
begin
insert into LogoutStats values(ora_login_user,sysdate);
end;

DDL Triggers are created for Create,Alter & Drop statements.

Here is a trigger that tracks created objects:-

create table CreatedObjects(CreatedByUser varchar(100),ObjectName varchar(100),ObjectType varchar(100),DateofCreation date)



CREATE or replace TRIGGER Databasecreateobjecttrigger AFTER CREATE
   ON database
begin
insert into CreatedObjects values(ora_login_user,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

For DML Triggers , please refer http://champakonline.blogspot.com/2010/10/some-triggers-in-oracle.html