MySQL Triggers - How to capture external web variables? (eg. web username, ip)
Posted
by Ken
on Stack Overflow
See other posts from Stack Overflow
or by Ken
Published on 2010-04-30T15:15:14Z
Indexed on
2010/04/30
15:17 UTC
Read the original article
Hit count: 459
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;
$$
© Stack Overflow or respective owner