触发器
触发器:分别对四家餐厅的就餐号码用触发器递增CREATE TRIGGER orderhistory_bi BEFORE INSERT ON orderhistoryFOR EACH ROWBEGINDECLARE dinnerno INTEGER;SELECT MAX(orderHistoryDinnerNo) INTO dinnerno FROM orderhistory WHERE DATE_FORMAT(orderhistorydinnertime,'%m-%d-%Y') = DATE_FORMAT(NEW.orderhistorydinnertime,'%m-%d-%Y');IF ISNULL(dinnerno) THENset NEW.orderHistoryDinnerNo = 1;ELSEset NEW.orderHistoryDinnerNo = dinnerno+1;END IF;ENDcreate view tb_dinnerno asselect orderhistory.orderhistorydinnerno,DATE_FORMAT(orderhistory.orderhistorydinnertime,'%Y-%m-%d') as orderhistorydinnertime ,canteen.canteenid,canteen.canteenname,menuitem.menuitemoffertime,menuitem.menuitemidfrom orderhistory,canteen,menuitemwhere orderhistory.menuitemid = menuitem.menuitemid and menuitem.canteenid = canteen.canteenidgroup by canteen.canteennamehaving max(orderhistory.orderhistorydinnertime);order by orderhistory.orderhistorydinnertime desc;select c.canteenname, o.orderhistorydinnerno from orderhistory o,menuitem m,canteen c where o.menuitemid = m.menuitemid and m.canteenid = c.canteenidand o.orderHistoryIsDeleted = 'N'CREATE TRIGGER orderhistory_bi BEFORE INSERT ON orderhistoryFOR EACH ROWBEGINDECLARE dinnerno INTEGER;DECLARE canteen INTEGER;SELECTcanteenid INTO canteen FROM menuitem WHERE menuitemid = NEW.menuitemid;SELECT max(orderhistorydinnerno) INTO dinnerno FROM tb_dinnerno WHERE DATE_FORMAT(orderhistorydinnertime,'%Y-%m-%d') = DATE_FORMAT(NEW.orderhistorydinnertime,'%Y-%m-%d') and canteenid = canteen;IF ISNULL(dinnerno) THENset NEW.orderHistoryDinnerNo = 1;ELSEset NEW.orderHistoryDinnerNo = dinnerno+1;END IF;END
页:
[1]