MySQLのtriggerを試す

最近の案件で削除フラグ連発のオシャレじゃないDB設計にやられてしまった。
だが、開発ノート的なところを覗くとトリガー使ってデータを移動する方法もあると聞き知ったこともあり、ちょっとやってみる。

やりたいことは、usersテーブルのレコードをDELETEしたら、DELETEしたレコードがdeleted_usersテーブルに移動されているようにしたい。

元テーブルとしてusersがある

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `salt` varchar(22) COLLATE utf8_unicode_ci NOT NULL,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `users_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

データを移動する用のテーブルを作成する

CREATE TABLE deleted_users LIKE users;

上記で作れなかったので、

CREATE TABLE `deleted_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `salt` varchar(22) COLLATE utf8_unicode_ci NOT NULL,
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `deleted_users_name_index` (`name`)
) ENGINE=InnoDB
;

トリガーの作成

DELIMITER //
CREATE TRIGGER deleted_user_insert
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO deleted_users VALUES (
  OLD.id, OLD.name, OLD.email, OLD.password, OLD.salt, OLD.last_login,
  OLD.status, OLD.remember_token, OLD.created_at, OLD.updated_at
);
END; //
DELIMITER ;

トリガーの削除

DROP TRIGGER deleted_user_insert;

usersテーブルにDELETE文を走らせると、確かに移動している。
ALTER文を実行するとトリガーは消えるらしいので、合わせて覚えとく。