drop trigger if exists ONS_DCSum_trg;
drop trigger if exists ONS_DiscountCards_bf_trg;

CREATE TRIGGER ONS_DiscountCards_bf_trg BEFORE INSERT ON `ONS_DiscountCards` FOR EACH ROW
BEGIN
  declare DCKey varchar(38);
  declare Summ float(9,3);

  select ds.primaryKey, ds.DCSum   
  into DCKey, Summ
  from ons_dcsum ds
  where ds.CardNumber = new.CardNumber
	and ds.State = 0
    and ds.DCTime + interval 6 day >= utc_time()
    and ds.DCSum = (
		select max(ds.DCSum)
		from ons_dcsum ds
		where ds.CardNumber = new.CardNumber
		  and ds.State = 0
		  and ds.DCTime + interval 6 day >= utc_time() ) LIMIT 1 ;

  if DCKey is not null then
	update ons_dcsum set State = 1 where primaryKey = DCKey;  
    
	if Summ > 500
    then
    	set new.State = 0;
	else 
    	set new.State = 1;
    end if;
  end if;

END;

CREATE TRIGGER ONS_DCSum_trg BEFORE INSERT ON `ONS_DCSum` FOR EACH ROW
BEGIN    
  declare cnt int(11);
  
  update ons_discountcards set State = (IF (new.DCSum >= 250, 0, 1))
  where State <> 0 and CardNumber = new.CardNumber;     
     
  select count(*) as cnt
  into cnt
  from ons_discountcards ds
  where ds.CardNumber = new.CardNumber;
  
  if cnt > 0
  	then 
    	SET new.State = 1;
  end if;
END;