與上一章節所提到的歷史表不同，歷史表需要經常翻查所以我們需要用到索引。審計表通常是數據歸檔，不允許修改，且基本上很少訪問。

		
CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `name` varchar(45) NOT NULL COMMENT '訂單名稱',
  `price` float NOT NULL COMMENT '價格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='訂單表'		
		
		

基于 order 表創建 order_audit 審計表

		
create table order_audit engine=archive as select * from `order`;
		
		

order_audit 表結構如下

		
CREATE TABLE `order_audit` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '訂單ID',
  `name` varchar(45) NOT NULL COMMENT '訂單名稱',
  `price` float NOT NULL COMMENT '價格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8		
		
		

創建插入和更新觸發器，用於插入和修改的時候同事寫入一份到歸檔表中。

		
DROP TRIGGER IF EXISTS `test`.`order_AFTER_INSERT`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`dba`@`%` TRIGGER `test`.`order_AFTER_INSERT` AFTER INSERT ON `order` FOR EACH ROW
BEGIN
	INSERT INTO order_audit SELECT * FROM `order` WHERE id = NEW.id; 
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `test`.`order_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`dba`@`%` TRIGGER `test`.`order_AFTER_UPDATE` AFTER UPDATE ON `order` FOR EACH ROW
BEGIN
	INSERT INTO order_audit SELECT * FROM `order` WHERE id = NEW.id; 
END$$
DELIMITER ;