這個實作要在XAMPP內，利用觸發器(Trigger)來監督表單的插入(Insert into)、更新(Update)、刪除(Delete From)，監督的意思是要知道誰在何時對資料表做了哪些更動資料的動作。例如哪個帳號在何時插入資料? 哪個帳號在何時將資料欄位更新? 哪個帳號在何時刪除資料?
要達成這樣的目的，有兩種方式 : 在寫程式的時候，將更動資料的動作寫入Log檔案，或是在資料庫中使用觸發器。前者是program-based，後者是SQL-based。
哪種方式比較好? 當然是SQL-based，因為如果使用program-based，只有透過程式介面去更動資料才會寫入Log檔案，如果從後台資料庫直接更動資料，program-based就無法監督。
以下就是以SQL-based的方式，只要資料表有被更動，觸發器就會依照規定的方式開始動作。
以PhpMyAdmin介面來實作
步驟一 : 先啟動Apache與MySQL後，點選Admin以進入PhpMyAdmin資料管理介面。
如下圖 :
步驟二 : 進入 PhpMyAdmin資料管理介面後，點選新增來建立一個資料庫。
如下圖 :
名稱填入 School，資料表的[字元集排序原則]選擇utf8_general_ci
utf8_general_ci 意思是使用字元集排序原則是 utf8 general，並且不分大小寫。其他例如utf8mb4_unicode_ci、utf8_general_cs、utf8mb4_unicode_cs等。
參考資料 :
https://www.mysql.tw/2013/03/mysql-character-set-collation.html
https://www.mysql.tw/2013/03/blog-post.html
如下圖 :
按下建立之後，就會增加一個資料庫 school。
如下圖 :
步驟三 : 再來就點選 school，然後點選SQL，這樣就能開始針對school資料庫來寫SQL語法。
如下圖 :
CREATE TABLE students (
stud_id INT PRIMARY KEY,
stud_name VARCHAR(50) NOT NULL
);
如下圖 :
CREATE TABLE audit_log (
log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
act_what VARCHAR(50),
act_who VARCHAR(50),
act_when DATETIME
);
如下圖 :
步驟四 : 接著就來建立Trigger。
以下列語法建立students_after_insert觸發器，然後按下執行。
DELIMITER //
CREATE TRIGGER students_after_insert
AFTER INSERT
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) VALUES (concat('A ', convert(NEW.stud_id,char)), (select user()), now()) //
DELIMITER ;
上面語句的意思，透過delimiter更改結尾方式，由原本的 ; 改為 //
因為語法中如果有 ; 的話，整個語句就開始執行，但我們希望整個邏輯寫完再送出去執行。
其他的語法，其實蠻固定的 :
DELIMITER //
CREATE TRIGGER [trigger name]
AFTER INSERT
ON [table name]
FOR EACH ROW
[執行的敘述] //
DELIMITER ;
如果執行的敘述是多行的話，會變成 :
DELIMITER //
CREATE TRIGGER [trigger name]
AFTER INSERT
ON [table name]
FOR EACH ROW
BEGIN
[執行的敘述#1] ;
[執行的敘述#2] ;
END //
DELIMTER;
上面的 AFTER INSERT 也可以是 BEFORE INSERT、AFTER DELETE、BEFORE DELETE、AFTER UPDATE、BEFORE UPDATE。
上面的select user()，就是指現在執行的帳號，now()就是現在的時間，而NEW.stud_id就是指新的stud_id。
如下圖 :
以下列語法建立students_after_update觸發器，然後按下執行。
DELIMITER //
CREATE TRIGGER students_after_update
AFTER UPDATE
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) values (concat('U ',convert(old.stud_id,char)),(SELECT USER()),NOW()) //
DELIMITER ;
當然有NEW.stud_id也就會有OLD.stud_id，就是更新前的stud_id，convert跟contact都是MySQL的既有函式。
如下圖 :
CREATE TRIGGER students_after_delete
AFTER DELETE
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) values (concat('D ',convert(old.stud_id,char)),(SELECT USER()),NOW());//
DELIMITER ;
步驟五 : 以插入、更新、刪除等語法來看看監看的結果。
以下列語法進行插入、更新、刪除 :
insert into students values (6, 'superman');
update students set stud_name='super' where stud_id=6;
delete from students where stud_id=6;
(不過上述的6只是範例資料，你自己要選一個不重複的stud_id值)
如下圖 :
步驟六 : 看看audit_log紀錄了什麼資料。
如下圖 :
以上表單，看到root@localhost這個帳號，在該時間做了A 6 (也就是插入一筆stud_id為6的資料)，然後又在另外時間做了U 6 (也就是更新stud_id為6的資料)，然後又在另外時間做了D 6 (也就是刪除stud_id為6的資料)。
只是由這個方式，並不知道更新或是刪除了什麼。
以指令方式來實作
步驟一 : 先啟動Apache與MySQL後，點選Shell進入指令模式。
如下圖 :
輸入以下指令登入 (如果root密碼空白)
mysql -u root
如下圖 :
輸入以下指令登入 (如果root密碼不是空白)
mysql -u root -p
如下圖 :
步驟二 : 建立一個新的資料庫。
CREATE DATABASE school
CHARACTER SET utf8
COLLATE utf8_general_ci;
如下圖 :
步驟三 : 選擇 school 資料庫並建立資料表。
USE school;
CREATE TABLE students (
stud_id INT PRIMARY KEY,
stud_name VARCHAR(50) NOT NULL
);
CREATE TABLE audit_log (
log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
act_what VARCHAR(50),
act_who VARCHAR(50),
act_when DATETIME
);
如下圖 :
步驟四 : 接著就來建立Trigger。
以下列語法建立students_after_insert觸發器。
DELIMITER //
CREATE TRIGGER students_after_insert
AFTER INSERT
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) VALUES (concat('ADD ', convert(NEW.stud_id,char)), (select user()), now()) //
DELIMITER ;
以下列語法建立students_after_update觸發器。
DELIMITER //
CREATE TRIGGER students_after_update
AFTER UPDATE
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) values (concat('U ',convert(old.stud_id,char)),(SELECT USER()),NOW()) //
DELIMITER ;
以下列語法建立students_after_delete觸發器。
CREATE TRIGGER students_after_delete
AFTER DELETE
ON students
FOR EACH ROW
Insert into audit_log(act_what, act_who, act_when) values (concat('D ',convert(old.stud_id,char)),(SELECT USER()),NOW());//
DELIMITER ;
如下圖 :
步驟五 : 以插入、更新、刪除等語法來看看監看的結果。
以下列語法進行插入、更新、刪除 :
insert into students values (6, 'superman');
update students set stud_name='super' where stud_id=6;
delete from students where stud_id=6;
如下圖 :
步驟六 : 看看audit_log紀錄了什麼資料。
SELECT * FROM audit_log;
[思考]
這個只是初步的監督結果，只知道更新了資料，但是不知道更新前是什麼資料? 只知道刪除了資料，但是不知道刪除的資料是什麼? 如果我們希望監督的觸發器要知道stud_name被更新前是什麼資料? 要知道stud_name被刪除前是什麼資料? 應該怎麼做?
~~~~~~~~~~~~~~~~~~~~~~~~~
我們還是使用school這個資料庫，把觸發器用在另外一個資料表 teachers (tid, tname)，並且監看的log資料表 audit_teacher (aid, act_what, act_who, act_when, aname)
以上 :
tid : teacher id
tname : teacher name
aid : audit id
act_what : 做了什麼動作
act_who : 誰做了
act_when : 何時做的
aname : 對哪個teacher name做動作
建立資料表 :
CREATE TABLE teachers (
tid INT PRIMARY KEY,
tname VARCHAR(50) NOT NULL
);
CREATE TABLE audit_teacher (
aid BIGINT PRIMARY KEY AUTO_INCREMENT,
act_what VARCHAR(50),
act_who VARCHAR(50),
act_when DATETIME,
aname VARCHAR(50)
);
建立觸發器 :
以下列語法建立teachers_after_insert觸發器。
DELIMITER //
CREATE TRIGGER teachers_after_insert
AFTER INSERT
ON teachers
FOR EACH ROW
Insert into audit_teacher(act_what, act_who, act_when, aname) VALUES (concat('A ', convert(NEW.tid,char)), user(), now(), NEW.tname) //
DELIMITER ;
以下列語法建立teachers_after_update觸發器。
DELIMITER //
CREATE TRIGGER teachers_after_update
AFTER UPDATE
ON teachers
FOR EACH ROW
Insert into audit_teacher(act_what, act_who, act_when, aname) values (concat('U ',convert(OLD.tid,char)), USER(),NOW(), OLD.tname) //
DELIMITER ;
以下列語法建立teachers_after_delete觸發器。
CREATE TRIGGER teachers_after_delete
AFTER DELETE
ON teachers
FOR EACH ROW
Insert into audit_teacher(act_what, act_who, act_when, aname) values (concat('D ',convert(OLD.tid,char)), USER(),NOW(), OLD.tname);//
DELIMITER ;
update teachers set tname='Tom Tom' where tid=1;
delete from teachers where tid=1;
0 留言