mysql 触发器介绍

create trigger triggerName 
 after/before insert/update/delete on tableName
 for each row   --这句话在mysql是固定的 
 begin 
    sql语句; 
 end; 

 

如下在插入之前自动生成座位号

DROP TRIGGER IF EXISTS `pxlb_zwh_insert`; 
CREATE TRIGGER `pxlb_zwh_insert`   
BEFORE INSERT ON `dj_pxlb`           
FOR EACH ROW
begin
IF new.zwh IS NULL OR new.zwh < 1 THEN
     SET new.zwh = (select IFNULL(max(t1.zwh),0)+1 from `dj_pxlb` as t1 where t1.ccbh=new.ccbh);
END IF;
end; 

 

 

 

DROP TRIGGER IF EXISTS t_make_data; 
CREATE TRIGGER t_make_data 
BEFORE INSERT ON t_tab1 
FOR EACH ROW
BEGIN  
DECLARE number_rand BIGINT(20) DEFAULT 4000 ;    --声明变量
SET number_rand = (SELECT CAST(RAND()*300+300 AS signed) AS rand);
SET new.number_rand = number_rand;
END;