Re: why the trigger is not working while refering other tables?

[Jay Pipes]

What version of MySQL are you using? If > 5.0.4, you can indeed use table references. Please explicitly use LOCK TABLES for the tables you'll be using in the trigger:

SET @@AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES registeredUser WRITE, Seller WRITE, Buyer WRITE, mysql.proc READ;
UPDATE registeredUser SET activation = 1 WHERE userID=123;
// Should insert appropriately into Seller and Buyer
UNLOCK TABLES;
COMMIT;

Obviously, I made up the WHERE userID=123 part; you can replace with the correct field.

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com