MySQL Trigger code needed

Hi all,

Can anyone create a trigger that says:

Copy Field1 FROM Table1 TO Field2 on Table2
When either a new record is created or modified

Cany anyone help please?

THanks
error77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi. You want to use CREATE TRIGGER (http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html). You want an AFTER INSERT, UPDATE trigger on Table1.

Whatever the key column that connects the two tables is what you would use in the trigger body FOR EACH ROW.

BEGIN
   UPDATE Table2
   SET Field2 = NEW.Field1
   WHERE key_column = OLD.key_column;

END

Without the full table structure,

Hope that helps!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure you can, but you should at least try to work it out:
to start trigger code:
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

help with update/join, if needed:
http://www.experts-exchange.com/A_1517.html

hope this helps
0
error77Author Commented:
I don't quite understand how they work even after reading the tutorial.
Are there any tool for creating triggers? And are these run on phpmyadmin SQL directly?

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Kevin CrossChief Technology OfficerCommented:
You run the DDL to create the trigger and then it will run on the MySQL server whenever the table is INSERTed or UPDATEd. What part do you NOT understand?
0
Kevin CrossChief Technology OfficerCommented:
In other words, we will help walking you through the parts that are difficult. I thought I gave you a good deal above (http:#36988698), though. Try putting it together yourself and post your attempt back here. If you are getting errors in PHPMyAdmin, then post those here also.
0
error77Author Commented:
OK, this is my first shot:

create trigger triggerName
after insert, update on table1
for each row
begin

UPDATE Table2
   SET Field2 = NEW.Field1 //cinfused here
   WHERE key_column = OLD.key_column; //confused here too
end

I'm I on the right track?
0
error77Author Commented:
Also...do driggers work between databases too?

THanks a lot
0
Kevin CrossChief Technology OfficerCommented:
By databases, do you mean schema? i.e., do you have database1.table1 and database2.table2 in the same MySQL instance?

create trigger triggerName
after insert, update on database1.table1
for each row
begin

UPDATE database2.Table2
   SET Field2 = NEW.Field1 //cinfused here
   WHERE key_column = OLD.key_column; //confused here too
end

OLD and NEW help you get at the "old" values and the "new" ones. So, because the trigger is on table1, you are getting the OLD value for key_column (i.e., the column that identifies the row in table2 that should be updated). Likewise, NEW holds the values inserted or updated to table1, so you can just grab Field1 (i.e., the field whose value you want to place in the second table).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
error77Author Commented:
Ref:
By databases, do you mean schema? i.e., do you have database1.table1 and database2.table2 in the same MySQL instance?
Yes, same MySQL instance ... different databases.


0
Kevin CrossChief Technology OfficerCommented:
Cool. Yep, it works as shown above. Hopefully that makes sense. If you need to JOIN to the original table1 and table2, you will find a3's article very useful.
0
error77Author Commented:
Thanks very much for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.