Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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
0
error77
Asked:
error77
  • 5
  • 5
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now