triggerdemo.sql

trigger示范程序

create or replace trigger insertemp before insert on works 
for each row 
begin  
insert into employee values (:new.employee_name,'Main',:new.company_name);
end;

create or replace trigger delemp_trig after delete on works
for each row 
begin 
delete from employee
where employee.employee_name = :old.employee_name;
end;


create or replace trigger overdraft_trigger after update on account 
for each row
when(new.balance < 0)
begin
insert into loan values
  (:new.account_number,:new.branch_name,-:new.balance);
insert into borrower values (
  select customer_name,account_number
  from depositor
  where :new.account_number = depositor.account_number);
update account set balance = 0
  where account.account_number = :new.account_number;
end;


/* 删除帐号时检查该帐户的所有者（客户）是否仍有其他帐户，
  若没有则把该客户从关系depositor中删掉*/
create or replace trigger del_Account_trig after delete on account
for each row 
declare 
	num number(3);
	lv_name depositor.customer_name%TYPE;
begin
  SELECT customer_name
  INTO lv_name;
  FROM depositor
  WHERE depositor.account_number = :old.account_number;

  SELECT count(account_number)
  into num
  from depositor
  where depositor.customer_name = lv_name;

  if num=0
  then  delete from depositor;
        where customer_name = lv_name;
  end if;
end;