触发器

触发器:
分别对四家餐厅的就餐号码用触发器递增

CREATE TRIGGER orderhistory_bi 
	BEFORE INSERT ON orderhistory  
	FOR EACH ROW
BEGIN
  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) 

  THEN	set NEW.orderHistoryDinnerNo = 1;
  ELSE
	set NEW.orderHistoryDinnerNo = dinnerno+1;
  END IF;
END

create view tb_dinnerno as
select orderhistory.orderhistorydinnerno,DATE_FORMAT(orderhistory.orderhistorydinnertime,'%Y-%m-%d') as orderhistorydinnertime ,canteen.canteenid,canteen.canteenname,menuitem.menuitemoffertime,menuitem.menuitemid
from orderhistory,canteen,menuitem
where orderhistory.menuitemid = menuitem.menuitemid and menuitem.canteenid = canteen.canteenid


group by canteen.canteenname
having 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 ROW
BEGIN
  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) 

  THEN	set NEW.orderHistoryDinnerNo = 1;
  ELSE
	set NEW.orderHistoryDinnerNo = dinnerno+1;
  END IF;
END