MySQL Triggers - How to capture external web variables? (eg. web username, ip)
- by Ken
Hi,
I'm looking to create an audit trail for my PHP web app's database (eg. capture inserts, updates, deletes).
MySQL triggers seem to be just the thing -- but how do I capture the IP address and the web username (as opposed to the mysql username, localhost) of the user who invoked the trigger?
Thanks so much.
-Ken
P.S. I'm working with this example code I found:
DROP TRIGGER IF EXISTS history_trigger $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
FOR EACH ROW
BEGIN
IF OLD.first_name != NEW.first_name
THEN
INSERT INTO history_clients
(
client_id ,
col ,
value ,
user_id ,
edit_time
)
VALUES
(
NEW.client_id,
'first_name',
NEW.first_name,
NEW.editor_id,
NEW.last_mod
);
END IF;
IF OLD.last_name != NEW.last_name
THEN
INSERT INTO history_clients
(
client_id ,
col ,
value ,
user_id ,
edit_time
)
VALUES
(
NEW.client_id,
'last_name',
NEW.last_name,
NEW.editor_id,
NEW.last_mod
);
END IF;
END;
$$
