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