how to create a trigger on adding new data?

PHPMaker General Discussion (PHPMaker)
1

hello everyone, I have two tables, the product_data and product_remarks:CREATE TABLE product_data (
product_id varchar(45) NOT NULL,
final_action varchar(255) DEFAULT NULL,
PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE product_remarks (
product_id varchar(32) NOT NULL,
id int(11) NOT NULL AUTO_INCREMENT,
remarks varchar(45) NOT NULL,
document_link varchar(255) NOT NULL,
other_remarks varchar(255) DEFAULT NULL,
timestamp datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;I want to create a trigger that will be executed everytime I add new data in product_remarks and the latest data I added will be replicated and will be updated on “final_action” field on product_data table. so far I have create a sql query which is working,UPDATE product_data A
INNER JOIN product_remarks B
on A.product_id = B.product_id
SET A.final_action = B.other_remarks
WHERE B.timestamp=(SELECT max(timestamp) FROM product_remarks B);Can someone please help me how to run this inside phpmaker since it is not working on the triggers inside mysql workbench, I have to run it manually in order to update the data on another table which is not what I want.Thank you.

2

You may try the Row_Inserted server event, see Server Events and Client Scripts in the help file.

3

Can you detail a little more what you need? because I am confused … if you already have triggers created in your database, these should be executed by themselves when executing the event: insert / update / deleted

4

Hello, i’ve already tried the trigger function inside the database but it is still not working so im thinking to use the update statement inside the phpmaker using server event but still no luck.can you please help me? thank you

5

arbei wrote:

You may try the > Row_Inserted > server event, see > Server Events and Client Scripts > in the help file.

Hello, i’ve try to use the update statement in the row inserted but still no luck. Can you please help me if im doing it right?Thanks.

// Row Inserted event
function Row_Inserted($rsold, &$rsnew) {
	//echo "Row Inserted"
$myResult = Execute("UPDATE pms_cfrr.product_data A
INNER JOIN pms_cfrr.product_remarks B
on A.product_id = B.product_id
SET A.final_action = B.other_remarks
WHERE B.timestamp=(SELECT max(timestamp) FROM pms_cfrr.product_remarks B)");

}
6

Hello. Thank you everyone, I’ve already resolve this problem using master/detail settings. Thanks