mysql - trigger table change

Question

I want to trigger an alert on screen when some changes made to my mysql tables using PHP.

Currently, I have many tables with different structures (Purchase Order, Sales Order, Users etc), my intention is to make a real-time application, but the problem is that I have already written 90% of the application.

I have a table with name activity It Contains:

id, table, action, dateTime, user

When some changes are made to Purchase Order table, then I need to log

(0,'TABLE A','INSERT','12/12/12','John')

so that I can show JOHN created a new Purchase Order.

Please note that I have already written many parts of the application, what will be the most possible way to achieve this ?

Answer 1

If your question is how to log changes in activity table then you can create triggers on every table (orders, sales, users etc) that you want to monitor like this:

CREATE TRIGGER `tg_orders_insert` AFTER INSERT ON orders
    FOR EACH ROW INSERT INTO `activity` (`id`, `table`, `action`, `date_time`, `user`) VALUES (NEW.id, 'orders', 'insert', NOW(), 'user1')  

CREATE TRIGGER `tg_orders_update` AFTER UPDATE ON orders
    FOR EACH ROW INSERT INTO `activity` (`id`, `table`, `action`, `date_time`, `user`) VALUES (NEW.id, 'orders', 'update', NOW(), 'user1')  

CREATE TRIGGER `tg_orders_delete` BEFORE DELETE ON orders
    FOR EACH ROW INSERT INTO `activity` (`id`, `table`, `action`, `date_time`, `user`) VALUES (OLD.id, 'orders', 'delete', NOW(), 'user1')