触发器

chenwq

触发器:分别对四家餐厅的就餐号码用触发器递增CREATE TRIGGER orderhistory_bi BEFORE INSERT ON orderhistory  FOR EACH ROWBEGIN  DECLARE 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.canteenid  and o.orderHistoryIsDeleted = 'N'CREATE TRIGGER orderhistory_bi BEFORE INSERT ON orderhistory  FOR EACH ROWBEGIN  DECLARE dinnerno INTEGER;  DECLARE canteen INTEGER;  SELECT  canteenid 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