title: create a trigger which will copy few data from other table after getting filtered from condition
body: <p>I have 5 tables. they are ---</p>
  
  <pre><code>create table customer
  (
  cust_name varchar2(20),
  cust_id number(5),
  date_of_birth date
  )
  
  insert into customer values('Mark',1,'10-JAN-1984');
  insert into customer values('Sam',2,'22-FEB-1990');
  
  create table bank
  (
  bank_name varchar2(20),
  bank_id number(5)
  )
  
  insert into bank values(20,'HDFC');
  insert into bank values(42,'ICICI');
  
  create table location
  (
  bank_id  number(5),
  loc_name  varchar2(20)
  )
  insert into location values(30,'Delhi');
  insert into location values(30,'USA');
  
  create table loan
  (
  cust_id number(5),
  bank_id number(5),
  st_date date
  intrest number(5)
  )
  
  
  
  create table audit
  (
  cust_id number(5),
  cust_name varchar2(20),
  date_of_birth date,
  bank_name varchar2(20),
  st_date date,
  loc_name  varchar2(20),
  interest number(5)
  )
  </code></pre>
  
  <p>I want to create a trigger on loan table, if any customer gets 0 interest, then details of that customer and his bank must be stored in audit table.</p>
  
  <p>so if execute this code--</p>
  
  <pre><code>insert into loan values(1,20,'20-DEC-2011',0);
  </code></pre>
  
  <p>That means this customer is getting 0 interest, so his records and his bank records must be stored in audit table.</p>
  
  <p>My trigger code is -- </p>
  
  <pre><code>create  or replace  trigger trg_loan
  after insert or update
  on loan
  for each row
  declare
  var_loc varchar2(50); 
  var_name varchar2(50);
  var_bankname varchar2(50);
  
  begin
  
  select cust_name into var_name from customer where cust_id = :new.cust_id;
  select bank_name into var_bankname from bank where bank_id = :new.bank_id;
  select loc_name into var_loc from location where loc_id = :new.loc_id;
  
      IF (:new.interest = 0) THEN
  insert into audit
  values(:new.cust_id,var_name,:new.date_of_birth,var_bankname,:new.st_date,var_loc,:new.interest);
  
      END IF; 
  end;
  </code></pre>
  
  <p>so this trigger is created  with compilation error.and it also does not  serve my interest.</p>