MySQL Trigger Mudah

Bagi table yang kita mahu pantau setiap data yang berubah, kita boleh menggunakan fungsi TRIGGER yang disediakan oleh database.

Kita ambil contoh,
Table: data
Fields: data1 dan data2.

Jadi, setiap perubahan pada table data dan field yang ada di dalamnya, akan kita rekodkan ke satu table lagi, iaitu table log atau history.

Cipta table Data

 CREATE TABLE data (  
       id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       timestamp  TIMESTAMP,  
       data1 VARCHAR(255) NOT NULL,  
       data2 DECIMAL(5,2) NOT NULL   
 );  

Cipta table data_log

 CREATE TABLE data_log (  
       histroyid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       action ENUM('I','U','D'),  
       id INT UNSIGNED NOT NULL,  
       timestamp TIMESTAMP,  
       data1 VARCHAR(255) NOT NULL,  
       data2 DECIMAL(5,2) NOT NULL   
 );  

Table data_log ini ada sedikit penambahan field iaitu:-
historyid = running number untuk table data_log itu sendiri
action = yang hanya akan menyimpan nilai I, U dan D. Insert, Update dan Delete.
timestamp = menyimpan rekod bila aktiviti tersebut dilakukan.

Selebihnya, duplikasi dari table asal.

Buat 3 jenis trigger untuk INSERT, UPDATE dan DELETE.

 DELIMITER $$  
 CREATE TRIGGER ai_data AFTER INSERT ON data  
      FOR EACH ROW  
      BEGIN  
       INSERT INTO data_log (action,id,timestamp,data1,data2)  
       VALUES('I',NEW.id,NOW(),NEW.data1,NEW.data2);  
      END$$  
 CREATE TRIGGER au_data AFTER UPDATE ON data  
      FOR EACH ROW  
      BEGIN  
       INSERT INTO data_log (action,id,timestamp,data1,data2)  
       VALUES('U',NEW.id,NOW(),NEW.data1,NEW.data2);  
 END$$  
 CREATE TRIGGER ad_data AFTER DELETE ON data  
      FOR EACH ROW  
      BEGIN  
       INSERT INTO data_log (action,id,timestamp,data1,data2)  
       VALUES('D',OLD.id,NOW(),OLD.data1,OLD.data2);  
 END$$  

Pada trigger INSERT dan UPDATE, kita masih boleh panggil data baru dan lama (NEW.xxx atau OLD.xxx). Tetapi pada trigger DELETE, cuma panggil data lama sahaja, kerana tiada sebarang data baru di sini.

Jika jalankan arahan menggunakan PhpMyAdmin, pada input Delimeter tu (yang berada di input bawah sekali selepas field SQL), setkan siap-siap untuk guna simbol $$. Ia bertujuan untuk kita running serentak arahan create trigger tadi.

Sekarang, dah boleh mula membuat sebarang aktiviti pada table data, sama ada nak insert, update dan delete. Dan lihat hasilnya pada table data_log.

-----------------------------

Sekiranya terlalu banyak column/field, agak leceh untuk buat table log. Kena taip satu-persatu field tiru yang sedia ada. Apa yang boleh di buat,

1. Create table serupa dengan table asal
2. tambah kemudian field untuk pantauan seperti logid, timestamp dan action.
3. susun semula kedudukan setiap field yang baru ditambah, letak di tempat pertama.

//1. Create table history serupa dengan table asal.
//Cara ini akan duplicate table asal beserta dengan datanya sekali (jika ada).
CREATE TABLE data_log AS SELECT * FROM data;

//2. Tambah field asas untuk table history
//Bagi field yang disetkan AUTO_INCREMENT, data akan masuk secara automatik sekiranya sudah ada baris data yang asal.
ALTER TABLE data_log ADD logid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE data_log ADD timestamp TIMESTAMP;
ALTER TABLE data_log ADD action ENUM('I','U','D');

//3. Susun semula kedudukan field
//Untuk history ID, letakkan di tempat pertama
ALTER TABLE `data_log` CHANGE `logid` `logid` INT(15) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

//Untuk lagi dua field, letakkannya selepas itu.
ALTER TABLE `data_log` CHANGE `timestamp` `timestamp` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `logid`;
ALTER TABLE `data_log` CHANGE `action` `action` ENUM('I','U','D') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `timestamp`;

//Update nilai 'I' pada data yang sudah sediada.
UPDATE `data_log` SET `action` = 'I';

Nota: Penggunaan nama `timestamp` dan `action` tidak sesuai digunakan. Jika ada perkataan yang lain, gunakanlah. Mungkin boleh guna `actiontime` dan `operation` atau `process`. Cari perkataan yang tidak digunakan secara standard dalam MySQL.