Insert data in current table and update another table MySQL

Yes, a trigger could be a good approach, but first, you should look more closely at your code:

$ok = $mysqli->query( $sql );
$ok1 = $mysqli->query( $sql );

It seems fairly clear that the second line should be executing the $sql1 query, not re-executing the $sql query.

Your design also seems less than optimal, for a couple of reasons:

• you're storing the same data in two different places in your database... the facilities table contains totals of product quantity, and that same information could be found by summing up the values found in the purchases table; information stored in more than one place is ripe for inconsistencies to creep in.

• if changing the above isn't practical, consider also that you're fetching $product_current_quantity from the facilities table, then letting your application add that value to the value in the $product_quantity and then you're stuffing that value back into the facilities table... this practice will also cause problems for you soon, if it hasn't already.

What you should be doing in this case is letting the database update the values in place:

UPDATE facilities
   SET product_current_quantity = product_current_quantity + $product_quantity
 WHERE ...                       # ^^^^^ use the column, not a variable

The big problem here is one of improper handling of concurrency. Assume two concurrent accesses involving the same facility/product:

Start with product_current_quantity of, for example, 5. Request #1 will add 3 to the quantity and Request #2 will add 2 to the quantity... and these two requests happen at almost exactly the same time. The total should now be 5 + 3 + 2 => 10, right? But...

request #1 reads product_current_quantity = 5
request #2 reads product_current_quantity = 5 (#1's update hasn't happened yet)
request #1 adds its local "5" + 3 and writes "8"
request #2 has already read "5" also, so it writes 5 + 2 => "7"
database value is now "7" instead of 10.

Letting the database do the math, and not your application, avoids this, because even if the two updates arrive at exactly the same time, each one will atomically modify the column's value, and the final result will be correct.

Additionally, this seems like a very appropriate place to be using transactions, because everything done inside a single transaction stands (COMMIT) or falls (ROLLBACK) together.

And, yes, a trigger would work, too:

# change the statement delimiter from semicolon to another symbol, such as "$$"
DELIMITER $$
DROP TRIGGER IF EXISTS purchases_ai $$
CREATE TRIGGER purchases_ai AFTER INSERT ON purchases FOR EACH ROW BEGIN

UPDATE facilities
   SET product_current_quantity = product_current_quantity + NEW.product_quantity
 WHERE facility_id = NEW.facility_id 
   AND id = NEW.product_id;

END $$
# change the delimiter back
DELIMITER ;

In a trigger, the NEW and OLD aliases refer to the row in the table before and after the query that was just executed. NEW applies only to INSERT and UPDATE triggers, and OLD applies only to UPDATE AND DELETE triggers. Triggers can fire either BEFORE or AFTER the affected row is actually changed. In the example above, this trigger fires AFTER INSERT -- immediately after the insert of the record into purchases.