当前位置：Gxlcms > 数据库问题 > MySQL笔记

MySQL笔记

时间：2021-07-01 10:21:17 帮助过：18人阅读

CREATE TRIGGER tr_student_insert AFTER INSERT ON student FOR EACH ROW 　　BEGIN 　　SET @txt = CONCAT(‘新增学生:[id:‘,new.id,‘,name:‘,new.name,‘,city:‘,new.city,‘,age:‘,new.age,‘,gradeId:‘,new.gradeId,‘]‘); 　　INSERT INTO student_log VALUES(NULL,‘新增‘,@txt,‘admin‘,NOW()); 　　END $ 　　DELIMITER ;

　　修改:

  1. <span style="color: #000000">　　DELIMITER $
  2. 　</span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TRIGGER</span> tr_student_update AFTER <span style="color: #0000ff">UPDATE</span> <span style="color: #0000ff">ON</span> student <span style="color: #0000ff">FOR</span><span style="color: #000000"> EACH ROW
  3. 　　</span><span style="color: #0000ff">BEGIN</span>
  4. 　　<span style="color: #0000ff">SET</span> <span style="color: #008000">@txt</span> <span style="color: #808080">=</span> CONCAT(<span style="color: #ff0000">‘</span><span style="color: #ff0000">修改学生:修改前[id:</span><span style="color: #ff0000">‘</span>,old.id,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,name:</span><span style="color: #ff0000">‘</span>,old.name,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,city:</span><span style="color: #ff0000">‘</span>,old.city,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,age:</span><span style="color: #ff0000">‘</span>,old.age,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,gradeId:</span><span style="color: #ff0000">‘</span>,old.gradeId,<span style="color: #ff0000">‘</span><span style="color: #ff0000">]修改后[id:</span><span style="color: #ff0000">‘</span>,new.id,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,name:</span><span style="color: #ff0000">‘</span>,new.name,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,city:</span><span style="color: #ff0000">‘</span>,new.city,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,age:</span><span style="color: #ff0000">‘</span>,new.age,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,gradeId:</span><span style="color: #ff0000">‘</span>,new.gradeId,<span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  5. 　　</span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student_log <span style="color: #0000ff">VALUES</span>(<span style="color: #0000ff">NULL</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">修改</span><span style="color: #ff0000">‘</span>,<span style="color: #008000">@txt</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">admin</span><span style="color: #ff0000">‘</span><span style="color: #000000">,NOW());
  6. 　　</span><span style="color: #0000ff">END</span><span style="color: #000000"> $
  7. 　　DELIMITER ;</span>

　　删除:

  1. <span style="color: #008080">1</span> <span style="color: #000000">　　DELIMITER $
  2. </span><span style="color: #008080">2</span> 　　<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TRIGGER</span> tr_student_delete AFTER <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">ON</span> student <span style="color: #0000ff">FOR</span><span style="color: #000000"> EACH ROW
  3. </span><span style="color: #008080">3</span> 　　<span style="color: #0000ff">BEGIN</span>
  4. <span style="color: #008080">4</span> 　　<span style="color: #0000ff">SET</span> <span style="color: #008000">@txt</span> <span style="color: #808080">=</span> CONCAT(<span style="color: #ff0000">‘</span><span style="color: #ff0000">删除学生:[id:</span><span style="color: #ff0000">‘</span>,old.id,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,name:</span><span style="color: #ff0000">‘</span>,old.name,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,city:</span><span style="color: #ff0000">‘</span>,old.city,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,age:</span><span style="color: #ff0000">‘</span>,old.age,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,gradeId:</span><span style="color: #ff0000">‘</span>,old.gradeId,<span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span><span style="color: #000000">);    
  5. </span><span style="color: #008080">5</span> 　　<span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student_log <span style="color: #0000ff">VALUES</span>(<span style="color: #0000ff">NULL</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">删除</span><span style="color: #ff0000">‘</span>,<span style="color: #008000">@txt</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">admin</span><span style="color: #ff0000">‘</span><span style="color: #000000">,NOW());
  6. </span><span style="color: #008080">6</span> 　　<span style="color: #0000ff">END</span><span style="color: #000000"> $
  7. </span><span style="color: #008080">7</span> 　　DELIMITER ;　

　　注意: 1,MySQL 不能将 insert,update,delete 放在一起,需分开建; 当前系统时间使用 NOW();
　　2,oracle支持 or replace 而mysql是不可以的
　　3,oracle在使用的变量要在begin前面声明。而mysql则用 @ 符号直接使用
　　4,oracle变量赋值用的是 := ，而mysql用的是 = 。
　　5,oracle新表和旧表用的是：new 和：old，而mysql用的是new 和old。

　　ORACLE 示例:

  1. <span style="color: #008080"> 1</span> 　<span style="color: #0000ff">CREATE</span> <span style="color: #808080">OR</span> <span style="color: #ff00ff">REPLACE</span> <span style="color: #0000ff">TRIGGER</span> tr_teacher AFTER <span style="color: #0000ff">INSERT</span> <span style="color: #808080">OR</span> <span style="color: #0000ff">UPDATE</span> <span style="color: #808080">OR</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">ON</span> teacher <span style="color: #0000ff">FOR</span><span style="color: #000000"> EACH ROW    
  2. </span><span style="color: #008080"> 2</span> 　　<span style="color: #0000ff">declare</span>
  3. <span style="color: #008080"> 3</span> 　　v_opt_type teacher_log.opt_type<span style="color: #808080">%</span><span style="color: #000000">TYPE;
  4. </span><span style="color: #008080"> 4</span> 　　v_old_value teacher_log.old_value<span style="color: #808080">%</span><span style="color: #000000">TYPE;
  5. </span><span style="color: #008080"> 5</span> 　　v_tno teacher_log.tno<span style="color: #808080">%</span><span style="color: #000000">TYPE;
  6. </span><span style="color: #008080"> 6</span> 　　<span style="color: #0000ff">begin</span>
  7. <span style="color: #008080"> 7</span> 　　<span style="color: #0000ff">IF</span> inserting <span style="color: #0000ff">THEN</span>
  8. <span style="color: #008080"> 8</span> 　　v_opt_type:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
  9. </span><span style="color: #008080"> 9</span> 　　v_tno:<span style="color: #808080">=</span><span style="color: #000000">:new.tno;
  10. </span><span style="color: #008080">10</span> 　　ELSIF deleting <span style="color: #0000ff">THEN</span>
  11. <span style="color: #008080">11</span> 　　v_opt_type:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">;
  12. </span><span style="color: #008080">12</span> 　　v_tno:<span style="color: #808080">=</span><span style="color: #000000">:old.tno;
  13. </span><span style="color: #008080">13</span> 　　v_old_value:<span style="color: #808080">=</span>:old.tname<span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span>:old.sal<span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #000000">:old.comm;
  14. </span><span style="color: #008080">14</span> 　　<span style="color: #0000ff">ELSE</span>
  15. <span style="color: #008080">15</span> 　　v_opt_type:<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">;
  16. </span><span style="color: #008080">16</span> 　　v_tno:<span style="color: #808080">=</span><span style="color: #000000">:old.tno;
  17. </span><span style="color: #008080">17</span> 　　v_old_value:<span style="color: #808080">=</span>:old.tname<span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span>:old.sal<span style="color: #808080">||</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span><span style="color: #808080">||</span><span style="color: #000000">:old.comm;
  18. </span><span style="color: #008080">18</span> 　　<span style="color: #0000ff">END</span> <span style="color: #0000ff">IF</span><span style="color: #000000">;
  19. </span><span style="color: #008080">19</span> 　　<span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> teacher_log <span style="color: #0000ff">VALUES</span><span style="color: #000000">(sq_log.nextval,v_old_value,SYSDATE,v_opt_type,v_tno);
  20. </span><span style="color: #008080">20</span> 　　<span style="color: #0000ff">end</span> tr_teacher;

 

MySQL笔记

标签：cte   文本文件   将不   负数   user   需要   signed   其他   sys   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！