triggers.txt

这是一个网上订票系统

--创建一个触发器，实现当用户退票的时候，向退款表refundment--
--中插入退款的所需的信息,规定退款金额为票价的80%--
SQL>@D:\schoolwork\数据库\第二次实验\trigger.sql;
create or replace trigger ReturnATicket before delete on speakfor
for each row
begin
  insert into refundment(id,ticketno,pass_name,money) (select ticket.
  id,ticket.ticketno,ticket.pass_name,ticket.price*0.8 from ticket
  where ticket.ticketno=:old.ticketno);
  update refundment set  (addr,postalcode)=(select addr,postalcode from 
  passenger where id=:old.id)
  where ticketno=:old.ticketno;
end ReturnATicket;



触发器已创建

--以下为对触发器的测试--
SQL>delete from speakfor where ticketno='10000008';
已删除 1 行。
--以下为对speakfor表的验证，看看是否删除成功--
SQL>select * from speakfor;
TICKETNO ID
-------- ------------------
10000001 1001
10000002 1002
10000003 1002
10000004 1003
10000005 1004
10000006 1005
10000007 1005

已选择7行。
--以下为对refundment表的验证,检验是否已将相关记录插入成功--
SQL>select * from refundment;

ID                 TICKETNO PASS_NAME            MONE ADDR                                     POSTAL
------------------ -------- -------------------- ---- ---------------------------------------- ------
1005               10000008 刘七                 1200 广州市从化                               510752

已选择 1 行。