Ok, say i have this table:
CREATE TABLE `User` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`email` VARCHAR(50) DEFAULT NULL,
`pass` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
and i have the same table:
CREATE TABLE `User_test` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`email` VARCHAR(50) DEFAULT NULL,
`pass` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
If I wanted to have the user_test table to be an exact copy of the
"user" table, would it be safe to say I have to create 3 triggers?
AFTER INSERT
AFTER UPDATE
AFTER DELETE
I figure it would be better to do the "AFTER" incase there was an issue
with doing the operation on the first table...
/*After INSERT*/
DELIMITER $$
USE `mydb`$$
DROP TRIGGER /*!50032 IF EXISTS */ `user_after_insert`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `user_insert` AFTER INSERT ON `User`
FOR EACH ROW BEGIN
INSERT INTO `mydb`.`User_test` VALUES (NEW.id, NEW.name,
NEW.email,
NEW.pass);
END;
$$
DELIMITER ;
/*After UPDATE*/
DELIMITER $$
USE `mydb`$$
DROP TRIGGER /*!50032 IF EXISTS */ `user_after_update`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `user_after_update` AFTER UPDATE ON `User`
FOR EACH ROW BEGIN
UPDATE `mydb`.`User_test` SET `name`=NEW.name,
`email`=NEW.email,
`pass`=NEW.pass WHERE `id`=NEW.id;
END;
$$
DELIMITER ;
/*After DELETE*/
DELIMITER $$
USE `mydb`$$
DROP TRIGGER /*!50032 IF EXISTS */ `user_after_delete`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `user_after_delete` AFTER DELETE ON `User`
FOR EACH ROW BEGIN
DELETE FROM `mydb`.`User_test` WHERE `id`=OLD.id LIMIT 1;
END;
$$
DELIMITER ;
Is there anything I should be cautious about? or anything that could be
a potential issue? In my testing, this all seems to be working just
fine, but I am looking for anything that I may have missed, or even
maybe an alternative to doing it this way.
The reason I am looking at doing this, is that the 'user' table is being
used by my app, as well as by another app (both of which are very
active) and when the table is being accessed by both apps, there is
noticeable lag on queries on this table... so i thought about doing this
to have an exact up to date copy that the other "app" can use so it
doesn't slow things down at all (the lag is about 1-2 seconds, which
isn't much, but can be a bother at times)
Thanks, and any/all assistance/opinions are welcomed!
Steve.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
