mysql触发器

触发器是由事件触发的操作，这些事件包括INSERT、UPDATE、DELETE事件。触发器是一种特殊的存储过程，它预定义了一些SQL，不用CALL来调用。当指定的事件发生的时候，触发器就会自动执行。

从设计模式的角度来看待，它其实就是一种观察者设计模式。从Java的角度来看待，它就是一个监听器。触发器触发后将会执行定义好的SQL语句。

1. 触发器概念 对当前表进行新增，更新，删除某一操作时会触发一个sql事件

insert into fx_userappreg
  (userregid,  
	 sysuserid,
	 userappid,
   openid,
   enable)
  select UUID(),
         new.sysuserid,
         fxu.userappid,
				 UUID(),
				 fxu.enable
    from fx_userapp fxu
   where fxu.enable=1

UPDATE fx_userappreg ft SET ft.enable = 1
 WHERE ft.sysuserid=(select fu.sysuserid from fx_systemuser fu WHERE fu.enable=1 and fu.sysuserid=new.sysuserid)
AND ft.userappid in 
(select fx.userappid from
 (select freg.userappid from fx_userappreg freg INNER  JOIN fx_userapp fuser ON fuser.userappid=freg.userappid  
 WHERE freg.sysuserid=new.sysuserid and fuser.enable=1 )
  as fx)

书写思路

SELECT fst.sysuserid FROM fx_systemuser fst WHERE fst.enable=1

SELECT UUID(),'sdD',UUID(),fst.sysuserid FROM fx_systemuser fst WHERE fst.enable=1


insert into fx_userappreg
  (userregid,  
	 sysuserid,
	 userappid,
   openid,
   enable)
  select UUID(),
         'dfs',
         fxu.userappid,
				 UUID(),
				 fxu.enable
    from fx_userapp fxu
   where fxu.enable=1

2. sql语句中如何为没有自增的主键添加数据

INSERT INTO TBL_ONE (ID, SOID, SNAME) values(UUID(),'dsk','ddf')

3. MySql中利用insert into select 准备数据uuid主键冲突

https://blog.csdn.net/lovelovelovelovelo/article/details/77507731

INSERT INTO TBL_ONE (ID, SOID, SNAME) 
SELECT
    UUID(), 
    TWO.ID,
    TWO.NAME
FROM
    TBL_TWO TWO;

4. insert与select集合批量插入数据

https://blog.csdn.net/FangX_u/article/details/80598137

(1) insert into tableName1 select * from tableName2;（没主键）

(2) insert into tableName(id, columnName1,columnName2,…) select 'XXX' as id, column1,column2 ,… from tableName2;(有主键:UUID)

5. sql里面插入语句insert后面的values关键字可省略

https://blog.csdn.net/qq_28817739/article/details/80910471

insert into teacher(id,name,salary)
select 3,'丁老师',5000 from teacher
where not exists(select * from teacher where id=3) limit 1;

6.insert into 配合 select批量插入

https://blog.csdn.net/Dopamy_BusyMonkey/article/details/81035041

insert into t_s_fun_custom
  (c_iden,
   c_fun_code,
   c_fun_name,
   c_fun_code_p,
   n_fun_type,
   n_order,
   n_default,
   c_ico_file,
   c_plan_code)
  select SEQU_S_FUN_CUSTOM.NEXTVAL,
         c_fun_code,
         c_fun_name,
         c_fun_code_p,
         n_fun_type,
         n_order,
         n_default + 2 as n_default,
         c_ico_file,
         replace(c_plan_code, ' ', '') || 'DefaultOldBiz' as c_plan_code
    from t_s_fun_custom
   where c_dv_app_cls = 'FUN_BIZ'
     and c_plan_code = ' '
     and n_default = 0;

7.数据库插入insert语句中添加嵌套查询select

https://blog.csdn.net/a287730887/article/details/84977937

insert into mytable(X,Y,Z)
Values((SELECT X from basetable WHERE ID = 8),(SELECT Y from basetable WHERE ID = 8),(SELECT Z from basetable WHERE ID = 8));

8. 时间函数增加

MySQL 时间函数加减计算
https://blog.csdn.net/qq_38486203/article/details/80654040#31-mysql-%E4%B8%BA%E6%97%A5%E6%9C%9F%E5%A2%9E%E5%8A%A0%E4%B8%80%E4%B8%AA%E6%97%B6%E9%97%B4%E9%97%B4%E9%9A%94

select UUID(),
         1,
         fxu.CFXXID,
				 0,
				 DATE_ADD(fxu.HYRQ,INTERVAL 1 week)   //为时间增加1个星期
    from ms_hy_cfxx fxu
		

9. navicat中使用查询语句查询数据库设置的触发器

SHOW TRIGGERS