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
-----/*觸發器*/
--滿足觸發條件時就會。自動執行。觸發器中的語句，可以保證某些操作之間的一致性
--可以層疊更改，可以引用其他表中的列
--事前觸發器可以獲取事前之前和新的欄位值,驗證一些條件和進行一些準備操作,在表儲存之前觸發*/
--事後觸發器是進行收尾工作,保證事務的完整性,在經表修改之後才能生效*/
--行級觸發器是對DML語句影響的每個行執行一次，如UPDATE語句影響多行，就會對每行都啟用一次觸發器。
-----建立
--建立只有一個執行語句的觸發器
 基本形式如下：
 create trigger 觸發器名 before|after 觸發事件 --觸發事件可以為insert,update,delete
 on 表名 for each row 執行語句 
 例：
 create trigger dept_trig1 before insert
 on department for each row
 insert into trigger_time values(now());
 --補坑,建立trigger_time表
 drop table if exists tigger_time;--拼錯了，少了一個'r'
 create table trigger_time(
  exec_time time
   )
 desc trigger_time
 --檢查trigger效果
 select * from department;
 insert into department values(1004,'銷售部','負責產品銷售','1號樓銷售大廳')--報錯，duplicate entry
 desc department --d_name是唯一性約束，不能取名‘銷售部’
 insert into department values(1004,'銷售1部','負責產品銷售','1號樓銷售大廳')
 select table_schema from information_schema.tables where table_name='trigger_time' --檢視trigger_time表屬於什麼庫
 select * from trigger_time --檢視trigger_time表是否被觸發器更新，Ok 
  
 --建立有多個執行語句的觸發器
 基本形式如下
 create trigger 觸發器名 before|after 觸發事件 --觸發事件可以為insert,update,delete
   on 表名 for each row --在指定表上逐行觸發
   begin
   執行語句1; 
   執行語句2; 
   .........;
   END
 /*MySQL預設以';'最為整段執行語句結束標誌，這裡用delimiter &&進行轉意成&&，&&表示整段語句結束，在IDE裡面應該可以不用轉意*/
 例：
 delimiter && 
 create trigger dept_trigzz after delete
 on department for each row
 begin
 insert into trigger_time values('21:01:01');
 insert into trigger_time values('22:01:01');
 end
 &&
 delimiter;
 show triggers
  
 --檢查trigger效果
 delete from department where d_id=1004;
 select * from trigger_time--成功insert，ok
  
/*一個表在相同觸發時間的相同觸發事件，只能建立一個觸發器*/
/*如department表中，觸發事件Insert,觸發事件為after的觸發器只能有一個，但可以定義觸發事件為Before的觸發器*/
---檢視觸發器
---1.show triggers
show triggers --show triggers無法查詢指定的觸發器，只用於觸發器較少的情況
---2.從triggers表中檢視觸發器資訊
select * from information_schema.triggers where tigger_name='觸發器名'
---觸發器的使用
--分別建立before insert和after insert兩個觸發器，比較執行順序
--建立before insert觸發器
create trigger before_insert before insert
on department for each row
insert into trigger_test values(null,'before_insert');
--建立after insert觸發器
create trigger after_insert after insert
on department for each row
insert into trigger_test values(null,'after_insert');
--補坑，建立trigger_test表
create table trigger_test(id int auto_increment primary key,
info varchar(10)
);
--驗證觸發器
insert into department values(1004,'銷售1部','負責產品銷售','1號樓銷售大廳');
delete from department where id = '1004'
desc department
delete from department where d_id = '1004'
insert into department values(1004,'銷售1部','負責產品銷售','1號樓銷售大廳');--info欄位報錯，長度不夠
--修改表中列的欄位長度，
alter table trigger_test change column info info varchar(15); --change可以同時改表欄位名與資料型別，但只修改欄位長度時需重複一遍欄位名
alter table trigger_test modify column info varchar(16);--modify只可以更改欄位資料型別
--刪除產生錯誤的插入列
delete from department where d_id = '1004'
--重新插入
insert into department values(1004,'銷售1部','負責產品銷售','1號樓銷售大廳');
--驗證觸發器執行結果
select * from trigger_test --第一條記錄為before_insert觸發器啟用後插入的‘before_insert’,第二條記錄為after_insert觸發器啟用後插入的‘after_insert’，ok
/*FBI warning*/
/*觸發器中不能包含事物處理的關鍵詞如：start transaction,commit,rollback,也不能包含call語句*/
/*在觸發器執行過程中，任何步驟出錯都會阻止程式繼續向下執行，但對產生觸發事件的普通表來說，已經insert/delete/update過的記錄是不能回滾的,更新過的資料將繼續保持在表中*/
---刪除觸發器
基本形式：
drop trigger 觸發器名
drop trigger 資料庫例項名.觸發器名 --觸發器是屬於資料庫例項全域性的
例：
drop trigger dept_trig1
--查詢是否還存在
select * from information_schema.triggers where trigger_name='dept_trig' --查詢無結果，Ok
/*FBI warning*/
/*當不再需要某個觸發器時，一定要將這個觸發器刪除,否則會造成資料發生意料之外的變化*/