MySQL学习笔记--触发器的创建与删除及使用注意事项

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

<code>-----/*触发器*/</code>

<code>--满足触发条件时就会。自动执行。触发器中的语句，可以保证某些操作之间的一致性</code>

<code>--可以层叠更改，可以引用其他表中的列</code>

<code>--事前触发器可以获取事前之前和新的字段值,验证一些条件和进行一些准备操作,在表保存之前触发*/</code>

<code>--事后触发器是进行收尾工作,保证事务的完整性,在经表修改之后才能生效*/</code>

<code>--行级触发器是对DML语句影响的每个行执行一次，如UPDATE语句影响多行，就会对每行都激活一次触发器。</code>

<code>-----创建</code>

<code>--创建只有一个执行语句的触发器</code>

<code> </code><code>基本形式如下：</code>

<code> </code><code>create</code> <code>trigger</code> <code>触发器名 before|</code><code>after</code> <code>触发事件 </code><code>--触发事件可以为insert,update,delete</code>

<code> </code><code>on</code> <code>表名 </code><code>for</code> <code>each row 执行语句 </code>

<code> </code><code>例：</code>

<code> </code><code>create</code> <code>trigger</code> <code>dept_trig1 before </code><code>insert</code>

<code> </code><code>on</code> <code>department </code><code>for</code> <code>each row</code>

<code> </code><code>insert</code> <code>into</code> <code>trigger_time </code><code>values</code><code>(now());</code>

<code> </code><code>--补坑,创建trigger_time表</code>

<code> </code><code>drop</code> <code>table</code> <code>if exists tigger_time;</code><code>--拼错了，少了一个'r'</code>

<code> </code><code>create</code> <code>table</code> <code>trigger_time(</code>

<code>  </code><code>exec_time </code><code>time</code>

<code>   </code><code>)</code>

<code> </code><code>desc</code> <code>trigger_time</code>

<code> </code><code>--检查trigger效果</code>

<code> </code><code>select</code> <code>* </code><code>from</code> <code>department;</code>

<code> </code><code>insert</code> <code>into</code> <code>department </code><code>values</code><code>(1004,</code><code>'销售部'</code><code>,</code><code>'负责产品销售'</code><code>,</code><code>'1号楼销售大厅'</code><code>)</code><code>--报错，duplicate entry</code>

<code> </code><code>desc</code> <code>department </code><code>--d_name是唯一性约束，不能取名‘销售部’</code>

<code> </code><code>insert</code> <code>into</code> <code>department </code><code>values</code><code>(1004,</code><code>'销售1部'</code><code>,</code><code>'负责产品销售'</code><code>,</code><code>'1号楼销售大厅'</code><code>)</code>

<code> </code><code>select</code> <code>table_schema </code><code>from</code> <code>information_schema.tables </code><code>where</code> <code>table_name=</code><code>'trigger_time'</code> <code>--查看trigger_time表属于什么库</code>

<code> </code><code>select</code> <code>* </code><code>from</code> <code>trigger_time </code><code>--查看trigger_time表是否被触发器更新，Ok </code>

<code> </code> 

<code> </code><code>--创建有多个执行语句的触发器</code>

<code> </code><code>基本形式如下</code>

<code>   </code><code>on</code> <code>表名 </code><code>for</code> <code>each row </code><code>--在指定表上逐行触发</code>

<code>   </code><code>begin</code>

<code>   </code><code>执行语句1; </code>

<code>   </code><code>执行语句2; </code>

<code>   </code><code>.........;</code>

<code>   </code><code>END</code>

<code> </code><code>/*MySQL默认以</code><code>';'</code><code>最为整段执行语句结束标志，这里用delimiter &amp;&amp;进行转意成&amp;&amp;，&amp;&amp;表示整段语句结束，在IDE里面应该可以不用转意*/</code>

<code> </code><code>delimiter &amp;&amp; </code>

<code> </code><code>create</code> <code>trigger</code> <code>dept_trigzz </code><code>after</code> <code>delete</code>

<code> </code><code>begin</code>

<code> </code><code>insert</code> <code>into</code> <code>trigger_time </code><code>values</code><code>(</code><code>'21:01:01'</code><code>);</code>

<code> </code><code>insert</code> <code>into</code> <code>trigger_time </code><code>values</code><code>(</code><code>'22:01:01'</code><code>);</code>

<code> </code><code>end</code>

<code> </code><code>&amp;&amp;</code>

<code> </code><code>delimiter;</code>

<code> </code><code>show triggers</code>

<code> </code><code>delete</code> <code>from</code> <code>department </code><code>where</code> <code>d_id=1004;</code>

<code> </code><code>select</code> <code>* </code><code>from</code> <code>trigger_time</code><code>--成功insert，ok</code>

<code>/*一个表在相同触发时间的相同触发事件，只能创建一个触发器*/</code>

<code>/*如department表中，触发事件</code><code>Insert</code><code>,触发事件为</code><code>after</code><code>的触发器只能有一个，但可以定义触发事件为Before的触发器*/</code>

<code>---查看触发器</code>

<code>---1.show triggers</code>

<code>show triggers </code><code>--show triggers无法查询指定的触发器，只用于触发器较少的情况</code>

<code>---2.从triggers表中查看触发器信息</code>

<code>select</code> <code>* </code><code>from</code> <code>information_schema.triggers </code><code>where</code> <code>tigger_name=</code><code>'触发器名'</code>

<code>---触发器的使用</code>

<code>--分别创建before insert和after insert两个触发器，比较执行顺序</code>

<code>--创建before insert触发器</code>

<code>create</code> <code>trigger</code> <code>before_insert before </code><code>insert</code>

<code>on</code> <code>department </code><code>for</code> <code>each row</code>

<code>insert</code> <code>into</code> <code>trigger_test </code><code>values</code><code>(</code><code>null</code><code>,</code><code>'before_insert'</code><code>);</code>

<code>--创建after insert触发器</code>

<code>create</code> <code>trigger</code> <code>after_insert </code><code>after</code> <code>insert</code>

<code>insert</code> <code>into</code> <code>trigger_test </code><code>values</code><code>(</code><code>null</code><code>,</code><code>'after_insert'</code><code>);</code>

<code>--补坑，创建trigger_test表</code>

<code>create</code> <code>table</code> <code>trigger_test(id </code><code>int</code> <code>auto_increment </code><code>primary</code> <code>key</code><code>,</code>

<code>info </code><code>varchar</code><code>(10)</code>

<code>);</code>

<code>--验证触发器</code>

<code>insert</code> <code>into</code> <code>department </code><code>values</code><code>(1004,</code><code>'销售1部'</code><code>,</code><code>'负责产品销售'</code><code>,</code><code>'1号楼销售大厅'</code><code>);</code>

<code>delete</code> <code>from</code> <code>department </code><code>where</code> <code>id = </code><code>'1004'</code>

<code>desc</code> <code>department</code>

<code>delete</code> <code>from</code> <code>department </code><code>where</code> <code>d_id = </code><code>'1004'</code>

<code>insert</code> <code>into</code> <code>department </code><code>values</code><code>(1004,</code><code>'销售1部'</code><code>,</code><code>'负责产品销售'</code><code>,</code><code>'1号楼销售大厅'</code><code>);</code><code>--info字段报错，长度不够</code>

<code>--修改表中列的字段长度，</code>

<code>alter</code> <code>table</code> <code>trigger_test change </code><code>column</code> <code>info info </code><code>varchar</code><code>(15); </code><code>--change可以同时改表字段名与数据类型，但只修改字段长度时需重复一遍字段名</code>

<code>alter</code> <code>table</code> <code>trigger_test </code><code>modify</code> <code>column</code> <code>info </code><code>varchar</code><code>(16);</code><code>--modify只可以更改字段数据类型</code>

<code>--删除产生错误的插入列</code>

<code>--重新插入</code>

<code>--验证触发器执行结果</code>

<code>select</code> <code>* </code><code>from</code> <code>trigger_test </code><code>--第一条记录为before_insert触发器激活后插入的‘before_insert’,第二条记录为after_insert触发器激活后插入的‘after_insert’，ok</code>

<code>/*FBI warning*/</code>

<code>/*触发器中不能包含事物处理的关键词如：start </code><code>transaction</code><code>,</code><code>commit</code><code>,</code><code>rollback</code><code>,也不能包含call语句*/</code>

<code>/*在触发器执行过程中，任何步骤出错都会阻止程序继续向下执行，但对产生触发事件的普通表来说，已经</code><code>insert</code><code>/</code><code>delete</code><code>/</code><code>update</code><code>过的记录是不能回滚的,更新过的数据将继续保持在表中*/</code>

<code>---删除触发器</code>

<code>基本形式：</code>

<code>drop</code> <code>trigger</code> <code>触发器名</code>

<code>drop</code> <code>trigger</code> <code>数据库实例名.触发器名 </code><code>--触发器是属于数据库实例全局的</code>

<code>例：</code>

<code>drop</code> <code>trigger</code> <code>dept_trig1</code>

<code>--查询是否还存在</code>

<code>select</code> <code>* </code><code>from</code> <code>information_schema.triggers </code><code>where</code> <code>trigger_name=</code><code>'dept_trig'</code> <code>--查询无结果，Ok</code>

<code>/*当不再需要某个触发器时，一定要将这个触发器删除,否则会造成数据发生意料之外的变化*/</code>

 本文转自 angry_frog 51CTO博客，原文链接:http://blog.51cto.com/l0vesql/1773648