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=listsearcharchive@lassosoft.com
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!