ORACLE触发器详细创建

--为cardInfo表创建insert事前触发器，实现添加账户信息时自动向userInfo表添加相应记录，需要判断改用户身份证号是否已存在，若存在则不添加，仅向cardInfo添加即可;

create or replace trigger tri_insert
  before insert on cardInfo
  for each row
begin
  declare
    card userInfo.User_Card %type;
  begin
  select user_card into card from userInfo where user_Id=:new.user_Id;  
  exception
      when no_data_found then
          insert into userInfo values(:new.user_Id,'张扬','男','15639700250','41147199101228466');
  end;
end;

commit;
insert into userInfo values(2015001,'张扬','男','15639700250','41147199101228466');

select * from userInfo;

--2、为tranInfo表创建insert触发器，实现添加交易记录时自动修改cardInfo表中相应账户的余额，注意应判断交易类型，若“支取”则余额减少，若“存入”则余额增加;

create or replace trigger tri_insert_tranifo
before insert on transInfo
for each row
  declare money cardInfo.current_money %type;
begin
    select current_money into money where card_id=:new.card_id;
  if :new.tran_type='支取' and money>=:new.tran_money  then
      update cardInfo set current_money=current_money-:new.tran_money where card_id=:new.card_id;
  else if  :new.tran_type='存入' then
          update cardInfo set current_money=current_money+:new.tran_money where card_id=:new.card_id;
    end if;
  end if;
          exception
      when no_data_found then
     
  dbms_output.put_line(:new.tran_money);
end;
--调用
declare
  trid number:=66622700||trunc(80000*dbms_random.value);
  cid char(18):=6227||to_char(sysdate,'YYYYMMDDHH24MISS');
begin  
insert into transInfo values(trid,'6221003',default,'存入',300.00);
end;
commit;
--3、为cardInfo表添加update触发器，实现修改余额时的业务规则应用，不允许余额小于0，否则报错。
create or replace trigger tri_cardInfo_update
before update on cardInfo
for each for
begin
  if :new.current_money<0then
      dbms_output.put_line('对不起，您的卡上当前余额不足！');
  end if;
end;

--　　4、为transInfo表添加delete触发器，实现删除交易记录时将删除的数据进行备份，备份到tempTrans表中。
create or replace trigger tri_transinfos_delete
after delete on transinfo for each row
begin
  insert into temptrans values(:old.tran_id,:old.card_id,:old.tran_date,:old.tran_type,:old.tran_money);
end;



commit;



select * from dept;