CODEWITHSUNDEEP
phpmysqltriggers
bensiu
bensiu

Reputation: 25604

Mysql trigger variable - find updating user

I have table with fields:

  1. id
  2. data1
  3. data2

next I want to set trigger that after update would write to log table changes:

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
   IF (NEW.data1 != OLD.data1) THEN
       INSERT INTO data_tracking set old_value = OLD.data1, new_value = NEW.data1, field = "data1";
   END IF;
   -- similar for data2
END$$

I also want to record in data_tracking table user that made change, however this user is not part of original UPDATE that trigger the trigger. I it a way to let trigger know what user need to be recorded ?

This is PHP based web service with multiple registered users, that can make changes to record via website - those user i would like to add to trigger.

Upvotes: 0

Views: 1368

Answers (2)

FrankPl
FrankPl

Reputation: 13315

As you want to use the user name that only is known to PHP, well, MySql cannot know which user triggered the change if this information is only available in PHP. This means you would at least have to pass the user in every update statement, either adding this as a column to all tables that need this kind of trigger, or do all the updates via stored procedures that get the user name as an additional parameter. Then you could get rid of all the triggers, as you would use stored procedures anyway that can do the logging as well as the updates.

Upvotes: 1

FrankPl
FrankPl

Reputation: 13315

Use CURRENT_USER, see the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Upvotes: 0

Related Questions