p1gTdhu9

1CUSTOMER
2---------
3CUSTOMER_ID PK
4CUSTOMER_NAME
5CUSTOMER_ADDRESS
6	
7CUSTOMER
8------------
9CUSTOMER_ID            PK
10CUSTOMER_VALID_FROM    PK
11CUSTOMER_VALID_UNTIL   PK
12CUSTOMER_STATUS
13CUSTOMER_USER
14CUSTOMER_NAME
15CUSTOMER_ADDRESS
16	
17ALTER TABLE MyDB.data ADD  `dt_datetime` DATETIME 
18   ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
19	
20CREATE TABLE MyDB.data_history LIKE MyDB.data
21
22ALTER TABLE MyDB.data_history MODIFY COLUMN primary_key_column int(11) NOT NULL, 
23   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
24   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action, 
25   ADD PRIMARY KEY (primary_key_column, revision)
26	
27DROP TRIGGER IF EXISTS MyDB.data__ai;
28DROP TRIGGER IF EXISTS MyDB.data__au;
29DROP TRIGGER IF EXISTS MyDB.data__bd;
30
31CREATE TRIGGER MyDB.data__ai AFTER INSERT ON mydb.bugs FOR EACH ROW
32    INSERT INTO MyDB.data_history SELECT 'insert', NULL, d.* 
33    FROM MyDB.data AS d WHERE d.id = NEW.id;
34
35CREATE TRIGGER MyDB.data__au AFTER UPDATE ON MyDB.data FOR EACH ROW
36    INSERT INTO MyDB.data_history SELECT 'update', NULL, d.*
37    FROM MyDB.data AS d WHERE d.id = NEW.id;
38
39CREATE TRIGGER MyDB.data__bd BEFORE DELETE ON MyDB.data FOR EACH ROW
40    INSERT INTO MyDB.data_history SELECT 'delete', NULL, d.* 
41    FROM MyDB.data AS d WHERE d.id = OLD.id;