----------------------------------------------------------
--每进行一次交易,就要调用触发器,自动扣除或增加账户金额
----------------------------------------------------------
create table account
(
customerName varchar2(30) primary key,
cardID varchar2(8),
currentMoney number
);
insert into account values('Daivd','10010001',5000);
insert into account values('Jason','10010002',3000);
create table trans
(
transDate date,
cardID varchar2(8),
transType varchar2(10),
transMoney number
);
insert into trans
values(sysdate,'10010001','取款',1000);
create or replace trigger trans_trigger
before insert
on trans
for each row
declare
v_currentMoney account.currentMoney%type;
begin
--判断类型
if :new.transType='取款' then
--取款
select currentMoney into v_currentMoney
from account
where cardID=:new.cardID;
if v_currentMoney < :new.transMoney then
raise_application_error(-20001,'余额不足');
end if;
update account
set currentMoney=currentMoney-:new.transMoney
where cardID=:new.cardID;
else
--存款
update account
set currentMoney=currentMoney+:new.transMoney
where cardID=:new.cardID;
end if;
exception
when no_data_found then
raise_application_error(-20002,'无效的帐户');
end;
--模式(schema)级触发器
create or replace trigger schema_trigger
before drop
on schema
begin
dbms_output.put_line('schema_trigger called');
dbms_output.put_line(ora_dict_obj_name);
dbms_output.put_line(ora_dict_obj_type);
if ora_dict_obj_name='ACCOUNT' then
raise_application_error(-20003,'ACCOUNT表不能被删除');
end if;
end;
drop table account;
--ora_dict_obj_name 操作对象名称
--ora_dict_obj_type 操作对象类型
--启用触发器
alter trigger schema_trigger enable;
--禁用触发器
alter trigger schema_trigger disable;
-------------------------自己写的-------------------
-----------------------测试表---------------------
create table Test
(
iKey int,
iValue int,
primary key(iKey)
);
create table Test_Log
(
iKey int,
iClass varchar(4)
);
create trigger Test_Tri after insert on Test
for each row
begin
if :new.iValue>90 then
insert into Test_log values(:new.iKey,1);
end if;
end;
insert into Test values(12,120);
insert into Test values(18,60);
insert into Test values(10,90);
|