Oracle数据库中的触发器练习

Oracle数据库中的触发器练习

一、表结构如图，其中orders的orderID是主键，orderItem的orderID是外键

 -- orders表
create table orders(
  2  orderID number primary key, -- 编号
  3  orderName varchar2(32), -- 名称
  4  orederprice number -- 单价
  5  )
  6  /
insert into orders values(1,'面包',3);
insert into orders values(2,'卫衣',45);
insert into orders values(3,'手机',1800);
insert into orders values(4,'鼠标',45);
insert into orders values(5,'键盘',130);


-- orderItem表
create table orderItem(
  2  orderItemID number primary key, -- 编号
  3  orderID number references orders(orderID), -- 订单编号
  4  orderItemNumber number -- 订购数量
  5  )
  6  /
insert into orderItem values(1,1,3);
insert into orderItem values(2,1,2);
insert into orderItem values(3,2,1);
insert into orderItem values(4,2,1);
insert into orderItem values(5,3,1);
insert into orderItem values(6,3,2);
insert into orderItem values(7,4,2);
insert into orderItem values(8,5,1);
insert into orderItem values(9,5,2);

1. 向订单信息表orderItem中插入数据时，如果数量小于等于0，直接将要插入的订单数量修改1，并提示用户订购数量必须大于0

-- 触发器
create or replace trigger t1
before
insert
on orderItem
for each row
when(new.orderItemNUmber < 1)
begin
 :new.orderItemNUmber:=1;  
end;

-- 触发触发器
insert into orderItem values(10,5,0);

-- 查询一下
select * from orderItem;

2. 当删除订单表orders中的订单时，如果该订单的编号被orderItem表引用，就先删除外键再删除主键，并提示用户‘已经将外键删除！

-- 触发器
create or replace trigger t2
before
delete
on orders
for each row
declare
   n number;
begin
   select count(*) into n from orderItem where orderID = :old.orderID;
   if n > 0 then
   delete from orderItem where orderID = :old.orderID;
   end if;
end;

-- 触发触发器
delete from orders where orderID = 1;

二、表结构：
账户信息表(cardInfo)：卡号(主键)，密码，余额

create table cardInfo(
  2  cardInfo_ID number primary key,
  3  cardInfo_Pwd varchar2(32),
  4  cardInfo_Balance number
  5  )
  6  /
insert into cardInfo values(1,'123',32);
insert into cardInfo values(2,'123',45);
insert into cardInfo values(3,'123',23);

交易信息表(transInfo)：编号，卡号(外键)，交易类型(只能是’支取’和’存入’)，交易金额，交易日期

create table transInfo(
  2  transInfo_ID number primary key,
  3  transInfo_CardInfoID references cardInfo(cardInfo_ID),
  4  transInfo_Deal varchar2(32) check(transInfo_Deal in('支取','存入')),
  5  transInfo_DealMoney number,
  6  transInfo_DealDate date
  7  )
  8  /
insert into transInfo values(1,1,'支取',12,sysdate);
insert into transInfo values(2,1,'支取',100,sysdate);
insert into transInfo values(3,1,'存入',1000,sysdate);
insert into transInfo values(4,2,'存入',500,sysdate);
insert into transInfo values(5,2,'支取',100,sysdate);
insert into transInfo values(6,3,'支取',100,sysdate);

3.当删除交易记录时，如果是一个月以内的交易记录就不能删除，并提示错误信息
提示：在删除前进行判断，引发异常可以回滚删除操作

-- 触发器
create or replace trigger t3
before
delete
on transInfo
for each row
when(months_between(sysdate,old.transInfo_DealDate) < 1)
begin
    raise_application_error(-20002,'这条交易记录的时间不超过一个月!');
end;

delete from transInfo where transInfo_id = 3;

4.当一个用户开户时(即向账户信息表插入一条记录时)，应该对应的在交易信息表中插入一条交易记录，交易类型为’存入’，金额为1，时间为系统当前时间

-- 触发器
create or replace trigger t4
after
insert
on cardInfo
for each row
declare
n number;
begin
 select max(transInfo_ID)into n from transInfo;
 insert into transInfo values(n+1,:new.cardInfo_ID,'存入',1,sysdate);
end;

insert into cardInfo values(4,'123',54);