数据库触发器应用

新增

BEGIN
    DECLARE startd INT; --定义数据类型   
    SET @i = 0 ;         --给数据赋值
    SET @nianx = new.prolimit;
    select left(FROM_UNIXTIME(startdate),4) INTO startd from                                   gx_i_preparation_of_expenditure_budget where item_no=new.item;        --SQL语句     

 WHILE @i<=startd DO           --循环                   
        SET @orders = (SELECT MAX(orderno)+0 FROM gx_projecrelated_tablebianone);
        set @ordera =SELECT CONCAT('0000000',@orders);
        INSERT INTO gx_projecrelated_tablebianone(item_no,year,createid,createtime,orderno) VALUES ( new.item_no,startd,new.createid,new.createtime,right(@orders,7));
        SET @i := @i+1;                      
END WHILE;                          
END

修改 +删除
begin
IF(old.promlimit<>new.promlimit OR old.startdate<>new.startdate) then        条件
    set @i=0;
    SET @nianx = new.prolimit;
    set @startd = (select left(FROM_UNIXTIME(startdate),4) );
    DELETE FROM gx_projecrelated_tablebianone WHERE item_no = item_no;
    WHILE @i<@nianx DO 
  SET @orders = (SELECT (MAX(orderno)+1) FROM gx_projecrelated_tablebianone);
  set @ordera = (SELECT CONCAT('00000000',@orders));
    INSERT INTO                 gx_projecrelated_tablebianone(item_no,year,updateid,updatetime,createid,createtime,orderno) VALUES ( new.item_no,@startd,new.updateid,new.updatetime,new.createid,new.createtime,right(@ordera,8));
  SET @i = @i+1;
    END WHILE;