帮忙写个触发器(oracle)
四个分部,一个分部5个工作人员,员工信息在北京集中管理,
每个分部有50个合同制快递员,
一年大概有50000个包裹,
如果跨区,使用最早的航班,然后由目的地分部的快递员配送。
在交接过程中保存相关数据,并可以追踪查询
Client (clno, clname, addr, city, tel, email)
clno = client number
clname = client name
多地址用户多个ID,即一个地址对应一个ID
Driver (drno, drname, addr, city, mobile, rate)
drno = driver’s number
rate是每公斤费率
Flight (flno, airline, depport, deptime, deptel, arrport, arrtime, arrtel, rate)
flno = flight number
depport = departure airport of the flight
deptel = telephone number of departure airport
arrport = arrival airport of the flight
arrport = telephone number of arrival airport
rate – 每公斤费率
Parcel (pno, fr_cl, to_cl, size, weight, clmval, charge)
pno, parcel number, 都是唯一的,北京的用B开头,上海的S开头,深圳的用Z开头,大连的用D开头。
clmval = 保价
charge = 收费
fr_cl references Client(clno)
to_cl references Client(clno)
Staff(staffno, city, tel, email, salary)
Delivery (parcel, date, loca, fr_ag, to_ag)
parcel references Parcel(pno)
fr_ag references Client(clno)或Driver(drno) 或Flight(flno)
to_ag references Client(clno)或Driver(drno) 或Flight(flno)