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

Some Triggers in Oracle

This series explains simple Triggers in Oracle.
This trigger will add Passbook entries to a Table Passbook based on modifications done to an Accounts Table.
The Bank Table

----------------------------------------- -------- ----------------------------
ACCNO NOT NULL NUMBER(38) Primary Key
BALANCE NOT NULLNUMBER(38)


The Passbook Table
----------------------------------------- -------- ----------------------------
ACCNO NUMBER(38) Foreign Key references Bank/(AccNo)
TRANSACTIONTYPENOT NULL VARCHAR2(10)
BALANCEBEFORE NUMBER(38)
BALANCEAFTER NUMBER(38)
TRANSACTIONAMOUNT NUMBER(38)
DATEOFTRANSACTIONDATE 
The Trigger
 
create or replace Trigger BankUpdateTrigger 
before update on Bank 
for each row
begin
if(:new.Balance<1000) then
RAISE_APPLICATION_ERROR(-20100,`Minimum Balance Violated. Cannot be less than 1000 ` | :new.Balance);
end if;
if(:new.Balance<:old.Balance) then
insert into Passbook values(:old.AccNo,`Withdraw`,:old.Balance,:new.Balance,:new.balance-:old.Balance,sysdate);
else
insert into Passbook values(:old.AccNo,`Deposit`,:old.Balance,:new.Balance,:new.balance-:old.Balance,sysdate);
end if;
end;


Database Triggers

This article creates a logon Trigger which stores the logins record in a table called Logins Table.


The Trigger
create or  replace trigger logintrigger 
after logon on database
begin

insert into LoginsTable values(ora_login_user,sysdate);
end;
The Table

create table LoginsTable(username varchar(30),logintime date)
Instead of Triggers
The table --- create table scientists(name varchar(50) primary key,subject varchar(50))
//*************************************
The View
create view vv as select name from scientists
//****************************************
The Trigger ---
create or replace Trigger ITrigger
instead of insert on vv
for each row
begin
insert into scientists values(:New.name,'Physics');
end;
//**********************************************
The Insert statement --- insert into vv values('Einstein');
//**********************************************
The select statement --- select * from scientists
//***********************************************
The Result ---
NAME    SUBJECT
Einstein    Physics