查看

//查询test数据库的触发器
SELECT * FROM information_schema.`TRIGGERS` WHERE TRIGGER_SCHEMA='test';
//在当前数据库的话
SHOW TRIGGERS;

创建

CREATE TRIGGER insert_order_sn BEFORE INSERT ON ecs_order_info FOR EACH ROW
BEGIN
   SET @dt = DATE_FORMAT(NOW(),'%Y%m%d');  
   SET @number = (SELECT count FROM ecs_order_counts WHERE date=@dt);
   IF @number IS NULL  THEN  
     insert into `ecs_order_counts` (`count`,`date`) VALUES(1,@dt);
     SET @number =0;
   END IF; 
   SET @number=@number+1;
   IF @number < 10 THEN
      SET @order_sn=CONCAT(@dt,'000',@number);
   ELSEIF @number>=10 and @number <100 THEN
      SET @order_sn=CONCAT(@dt,'00',@number);
   ELSEIF @number>=100 and @number <1000 THEN
      SET @order_sn=CONCAT(@dt,'0',@number);
   ELSEIF @number>=1000 THEN
      SET @order_sn=CONCAT(@dt,@number);
   END IF;
   SET NEW.order_sn=@order_sn;
   UPDATE `ecs_order_counts` set `count`=@number where date=@dt;
END

删除

DROP TRIGGER insert_order_sn